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.
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.
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;
It is important to note that all SQL statements are case-insensitive, so
SELECT is the same as
select, which is the same as
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.
SELECT * is usually read as “Select all columns”.
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;
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;
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;
|Bob works as a Manager|
|Eve works as a Developer|
|Max works as a Janitor|
|Kim works as a Manager|
|Joe works as a Developer|
|Sam works as a Janitor|