SQL SELECT Statement

The SQL statement that you will probably use most often is the SELECT statement. Its purpose is to retrieve data from one or more tables of the database (or even several databases) and display it.

The result of a SELECT statement is another table, also known as a result set.

Syntax

The simplest form of the SELECT statement contains a SELECT list with the FROM clause. It has the following syntax:

SELECT column_name(s)
FROM table_name;

In practice, however, there are always several more clauses in a SELECT statement than in the statement. The following is the syntax of a SELECT statement, with almost all possible clauses included:

SELECT column_name(s)
[INTO new_table_name]
FROM table_name
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression];

Note that anything inside the square bracket is an optional clause.

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

SELECT a Column

To use SELECT to retrieve table data, you must specify two things – what you want to select (column_name), and from where you want to select it (table_name).

For example, this simple SELECT statement retrieves a single column called ‘Name’ from the ‘Employees’ table.

SELECT Name
FROM Employees;

SELECT Multiple Columns

The same SELECT statement can be used to retrieve multiple columns from a table. Just specify the desired column names after the SELECT keyword, and separate them with a comma.

The following SELECT statement retrieves two columns from the ‘Employees’ table:

SELECT Name, Job
FROM Employees;

Note that the value of each column you name is retrieved in the order specified in the SELECT clause.

SELECT * (All Columns)

In addition to being able to specify desired columns, SELECT can also retrieve all columns without having to list them individually. This is done by specifying the * wildcard character in place of column names.

Let’s retrieve all columns from the table.

SELECT *
FROM Employees;

When you specify a wildcard *, the columns are retrieved in the same order as they appear in the table’s definition.

Limiting Results

The SELECT statement returns all matched rows by default. What if you want to return a specific number of rows?

This is possible, but unfortunately, it is one of those situations where not all SQL implementations are created equal. Each DBMS has its own clause to limit the number of entries to be retrieved.

For example, below queries retrieve only the first three rows.

--for MS SQL and MS Access
SELECT TOP 3 *
FROM Employees;

--for DB2
SELECT *
FROM Employees
FETCH FIRST 3 ROWS ONLY;

--for Oracle
SELECT *
FROM Employees
WHERE ROWNUM <=3;

--for MySQL, MariaDB, PostgreSQL, and SQLite
SELECT *
FROM Employees
LIMIT 3;

Removing Duplicates

In some cases, a query might return duplicate rows of data. For example, if you were to retrieve the list of jobs, you would see the following:

SELECT Job
FROM Employees;

What you probably want in this case is the list of unique jobs. You can achieve this by adding the keyword DISTINCT just after the SELECT keyword.

SELECT DISTINCT Job
FROM Employees;

Column Aliases

Although the SQL server will generate labels for the columns returned by your queries, you may want to assign your own labels. You can do so by adding a column alias after each column of your SELECT clause.

Here’s the previous query against the ‘Employees’ table with column alias ‘Job_Titles’ applied to the column ‘Job’:

SELECT DISTINCT Job Job_Titles
FROM Employees;

An alias is just an alternate name for a column. It does not actually change the name of the column in the table, but it gives it a new name within the scope of the SELECT statement.

You can also include the AS keyword before the alias name. Use of the AS keyword is optional, but you can use it to improve readability.

SELECT DISTINCT Job AS Job_Titles
FROM Employees;

Expressions in SELECT Statement

The SELECT statement can do far more than simply retrieve columns. You can also perform some calculations on one or more columns and include them in a result.

Suppose you wanted to generate a calculated column called ‘Tax_paid’ that is 22% of the salary. You could issue the following SELECT query to calculate this dynamically.

SELECT Name,
       Salary,
       0.22 * Salary AS Tax_paid
FROM Employees;

Notice that the ‘Tax_paid’ column is not stored in the table, but computed on-the-fly. This is a powerful feature of SQL, which allows us to keep stored data simple and use queries to perform complex calculations on top of it.

Here are some more things you can include in your SELECT clause.

• Literals, such as strings or numbers
• Built-in function calls
• User-defined function calls

The next query demonstrates the use of a table column, a literal, an expression, and a built-in function call in a single query against the ‘Employees’ table:

SELECT Name,
       'Active' AS Status,
	   0.22 * Salary AS Tax_paid,
	   UPPER(Job) AS Job
FROM Employees;

Concatenating Column Values

You can use expressions not only with numbers but also with text and other data types.

For example, a helpful operator to use with text is concatenation, which merges two or more pieces of data together.

--for SQL server
SELECT Name + ' works as a ' + Job AS Description
FROM Employees;

--for DB2, Oracle and PostgreSQL
SELECT Name || ' works as a ' || Job AS Description
FROM Employees;

--for MySQL
SELECT CONCAT(Name, ' works as a ', Job) AS Description
FROM Employees;