SQL SELECT Statement

SQL SELECT is probably the most widely used SQL statement. That’s why we have decided to investigate options that can be used in the query.

Besides, the article contains a bunch of use cases of the SELECT statement with practical examples and lists three simple ways to retrieve data with the help of dbForge Studio for SQL Server.

Contents

• Introduction to SQL SELECT query
• The general syntax of the SELECT command
  • SELECT statement options
• SQL SELECT statement examples
  • How to exclude a column from the SELECT
  • Select data from a specific column in SQL
  • SELECT data from multiple tables with JOIN
  • Select rows where a particular field does not contain a specific value
• SELECT statements with dbForge Studio for SQL Server
• Conclusion

Introduction to SQL SELECT query

SQL SELECT statement is used for fetching some data from a database. The statement is just a query that claims what data to be retrieved, where to search for it, and how to modify it before returning.

In most cases, the SELECT query is run with FROM. SELECT states columns and FROM indicates a table where these columns are located.

The general syntax of the SELECT command

The basic syntax of the query is as follows:

SELECT <column1>, <column2>, ...
FROM <table>;

Parameters:

• <column1>, <column2>: the names of the columns you want to select data from
• <table>: the name of the table where the specified columns are located

SELECT statement options

SQL SELECT has different clauses to manage the data output. They are: FROM, AS, GROUP BY, HAVING, INTO, ORDER BY, * (asterisk). Let’s see how we can use each clause within the SELECT syntax.

1. FROM is used to specify a table name where a necessary column with data is located.

Syntax:

SELECT <column>
<table>; 

Parameters:

• <column>: the name of the column you want to select data from
• <table>: the name of the table where a specified column is located

2. AS is used to create a temporary name for the column headings. This method lets create more clear column headings. AS is optional and can be present in the query for readability purposes.

Syntax:

SELECT <column> [ AS <new_column> ] 
FROM <table> [ AS <new_table> ]; 

OR

SELECT <column> [ <new_column> ]
FROM <table> [ <new_table> ]; 

Parameters:

• <column>: the name of the column to be renamed
• <new_column>: a new name that must be assigned to the column
• <table>: the name of the table to be renamed
• <new_table>: a new name that must be assigned to table

3. GROUP BY is used to group results with similar data. There are some important things you should know about the clause:

• GROUP BY displays one record for each group.
• GROUP BY is used with aggregate functions COUNT, MAX, MIN, SUM, AVG etc.
• GROUP BY follows the WHERE clause, but precedes the ORDER BY clause in a query.

Syntax:

SELECT <column1>, SUM(<column2>)
FROM <table>
GROUP BY <grouping_column>;

Parameters:

• <column1>: the name of the column you want to select data from
• <column2>: the name of a numeric column you want to retrieve a total sum from
• <table>: the name of the table where a specified column is located
• <grouping_column>: the name of the column by which the results will be grouped

4. HAVING is used to define a search condition. The clause is used in combination with GROUP BY.

Syntax:

SELECT <column1>, SUM(<column2>)
FROM <table>
GROUP BY <grouping_column>
HAVING <condition>;

Parameters:

• <column1>: the name of the column you want to select data from
• <column2>: the name of a numeric column you want to retrieve a total sum from
• <table>: the name of the table where a specified column is located
• <grouping_column>: the name of the column by which the results will be grouped
• <condition>: an additional search condition that will be applied for aggregated results

5. INTO is used to create a new table and copy the retrieved results into it.

Syntax:

SELECT <column> INTO <new_table>
FROM <table>
WHERE <condition>;

Parameters:

• <column>: the name of the column you want to select and copy into a new table
• <new_table>: the name of a new table to be created for further copying a specified column
• <table>: the name of the table where a specified column is located
• <condition>: a condition for filtering and fetching only the necessary records

6. ORDER BY is used to filter retrieved results. The sorting can be organized in ascending (a default one) and descending order.

Syntax:

SELECT <column1>
FROM <table>
ORDER BY <column2>;

Parameters:

• <column1>: the name of the column you want to select data from
• <table>: the name of the table where a specified column is located
• <column2>: the name of the column to sort retrieved results

7. * (asterisk) is used to get all columns and rows from a table.

Syntax:

SELECT *
FROM <table>;

Parameters:

• <table>: the name of the table you want to fetch all available columns and rows from

SQL SELECT statement examples

In this section, we are going to review various cases of using the SELECT query on the examples based on two tables: Customer and Country.

The Customer table:

customer_id	name	country_id
1	Tom Crawford	3
2	Mia Chesterfield	1
3	Sally Colland	4
4	Peter Cromwell	2

The Country table:

country_id	name
1	Canada
2	New Zealand
3	Thailand
4	Poland

Select all columns from a table

To select all columns from a table, the SELECT syntax is as follows:

SELECT *
FROM customer; 

You will retrieve all available columns and rows from the Customer table.

How to exclude a column from the SELECT

Suppose that you want to select all the columns from the Customer table, except name. In this case, a syntax example will look as follows:

SELECT customer_id, country_id
FROM customer;

Select data from specific column in SQL

To select data from a specific column in a table, see the query below:

SELECT name
FROM customer;

In the output, you will see the data available in the name column in the Customer table.

SELECT data from multiple tables with JOIN

It is also possible to select data from multiple tables with the help of JOIN clauses. There are several different types of JOIN clauses in SQL. For more information, please refer to Different types of JOINS in SQL Server.

In this section, we will provide the syntax example of SELECT with INNER JOIN to obtain data from multiple tables.

SELECT cs.name AS customer_name, cn.name AS country_name
FROM customer AS cs
INNER JOIN country AS cn
ON cs.country_id=cn.country_id;

The query will output data from the country_id columns from both the Customer and the Country tables.

Select rows where a particular field does not contain a specific value

Imagine that you need to identify rows in a dataset where a specific field does not contain a particular value. You can complete this task with the help of the NOT LIKE, NOT IN, NOT EXISTS, IS NULL, and <> (Not Equal To) operators. Let’s review each of them from a practical perspective.

The syntax with NOT LIKE looks as follows:

SELECT *
FROM customer
WHERE first_name NOT LIKE '%ry%';

In this query, WHERE first_name NOT LIKE ‘%ry%’ is a condition that checks whether the value in field1 doe not contain ry. The % symbols are wildcard characters used in SQL to match any sequence of characters. Therefore, %ry% implies a string that has ry wrapped in any character sequence. The usage of the
NOT LIKE operator means that it selects rows where first_name does not match the specified pattern.

Adding NOT IN serves the same purpose as the previous query.

SELECT
     *
 FROM customer
 WHERE first_name NOT IN ('Mary', 'Lisa');

NOT IN indicates a condition where the column value is not equal to any of the values provided within the parentheses. In other words, the operator checks if field1 is not equal to Mary and Lisa.

NOT EXISTS is another alternative way to retrieve rows where the field does
not contain the required value.

SELECT
       *
FROM customer
WHERE NOT EXISTS (SELECT
       *
      FROM address
      WHERE postal_code = 73087);

The two first strings are clear, but let’s dive into the other part of the syntax.

• WHERE NOT EXISTS ( . . . ) is a requirement verifying that no rows
  meet the conditions specified within the subquery.
• SELECT * FROM address WHERE postal_code = 73087 is a subquery that
  selects the value from address where the postal_code column equals to
  73087.

The IS NULL operator can be used to determine whether the value in field1 is
NULL or not equal to the value set in $x. For example:

SELECT *
 FROM customer
 WHERE first_name IS NULL OR first_name != 'Lisa';

In this example, OR is a logical operator that indicates either the condition on its left or its right should be true for the entire condition to be true.

With <> (not equal to), you can check if two values are not equal.

SELECT *
 FROM customer
 WHERE first_name <> 'Mary';

WHERE first_name <> ‘Mary’ is a condition for verifying if the value in the first_name column is not equal to Mary.

So, we’ve listed some common methods to find rows with a field that does not
contain a specific value. Choose the one that meets your needs and go for it!

SELECT statements with dbForge Studio for SQL Server

With dbForge Studio for SQL Server, you can retrieve data from a table in three simple ways. Let’s review each of them.

Way 1 – Using the Code completion feature:

1. In the SQL Editor, start typing SELECT. The drop-down menu will appear.

2. Double-click SELECT:

3. Fill the query and click Execute on the standard toolbar.

Way 2 – Using the Generate Script As option:

1. In Database Explorer, right-click the necessary table and navigate to Generate Script As > SELECT > To New SQL Window:

2. In the SQL document that opens, adjust the query and click Execute on the standard toolbar.

Way 3–using the Retrieve Data option:

This method allows getting all columns from a required table.

In Database Explorer, right-click the necessary table, then click Retrieve Data:

And voila, you will be able to see the data from all columns available in the table.

Conclusion

To summarize, we have explored the SELECT statement with various options, supported each case with a specific example. Retrieving data becomes a simple process when you know how to use the query and in what order to apply its options. dbForge Studio for SQL Server can significantly simplify this task. Download its 30-day trial version and experience all the advantages.