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 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 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.