SQL SELECT

Summary: in this tutorial, you will learn how to use the SQL SELECT statement to query data from a single table.

Introduction to SQL SELECT statement

The SQL SELECT statement selects data from one or more tables. The following shows the basic syntax of the SELECT statement that selects data from a single table.

SELECT 
    select_list
FROM
    table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify a list of comma-separated columns from the table in the SELECT clause.
• Then, specify the table name in the FROM clause.

When evaluating the SELECT statement, the database system evaluates the FROM clause first and then the SELECT clause. It’s like from a table, select data from these columns.

The semicolon (;) is not part of a query. The database server uses it to separate two SQL statements.

For example, if you execute two SQL SELECT statements, you need to separate them using the semicolon (;). Check out the SQL syntax for more information.

If you want to query data from all the columns of the table, you can use the asterisk (*) operator instead if specifying all the column names:

SELECT * FROM table_name;

SQL is case-insensitive. Therefore, the SELECT and select keywords have the same meaning.

By convention, we will use the uppercase letters for the SQL keywords, such as SELECT and FROM and the lowercase letters for the identifiers such as table and column names. This convention makes the SQL statements more readable.

SQL SELECT statement examples

We’ll use the employees table in the sample database for demonstration purposes.

1) SQL SELECT – selecting data from all columns example

The following example uses the SQL SELECT statement to get data from all the rows and columns in the employees table:

SELECT * FROM employees;
Code language: SQL (Structured Query Language) (sql)

Try It

The following shows the result sets returned by the database server. It’s like a spreadsheet that contains rows and columns with a heading:

+-------------+-------------+-------------+-----------------------------------+--------------+------------+--------+----------+------------+---------------+
| employee_id | first_name  | last_name   | email                             | phone_number | hire_date  | job_id | salary   | manager_id | department_id |
+-------------+-------------+-------------+-----------------------------------+--------------+------------+--------+----------+------------+---------------+
|         100 | Steven      | King        | steven.king@sqltutorial.org       | 515.123.4567 | 1987-06-17 |      4 | 24000.00 |       NULL |             9 |
|         101 | Neena       | Kochhar     | neena.kochhar@sqltutorial.org     | 515.123.4568 | 1989-09-21 |      5 | 17000.00 |        100 |             9 |
|         102 | Lex         | De Haan     | lex.de haan@sqltutorial.org       | 515.123.4569 | 1993-01-13 |      5 | 17000.00 |        100 |             9 |
|         103 | Alexander   | Hunold      | alexander.hunold@sqltutorial.org  | 590.423.4567 | 1990-01-03 |      9 |  9000.00 |        102 |             6 |
|         104 | Bruce       | Ernst       | bruce.ernst@sqltutorial.org       | 590.423.4568 | 1991-05-21 |      9 |  6000.00 |        103 |             6 |
|         105 | David       | Austin      | david.austin@sqltutorial.org      | 590.423.4569 | 1997-06-25 |      9 |  4800.00 |        103 |             6 |
|         106 | Valli       | Pataballa   | valli.pataballa@sqltutorial.org   | 590.423.4560 | 1998-02-05 |      9 |  4800.00 |        103 |             6 |
|         107 | Diana       | Lorentz     | diana.lorentz@sqltutorial.org     | 590.423.5567 | 1999-02-07 |      9 |  4200.00 |        103 |             6 |
|         108 | Nancy       | Greenberg   | nancy.greenberg@sqltutorial.org   | 515.124.4569 | 1994-08-17 |      7 | 12000.00 |        101 |            10 |
...
Code language: plaintext (plaintext)

The SELECT * is read as the select star. The select star is helpful for ad-hoc queries only.

For the application development, you should avoid using the select star for the following reason.

The select * returns data from all columns of a table. Often, the application doesn’t need data from all the columns but one or some columns.

If you use the select *, the database needs more time to read data from the disk and transfer it to the application. This often results in poor performance if the table contains many columns with a lot of data.

2) SQL SELECT – selecting data from specific columns

To select data from specific columns, you can specify the column list after the SELECT clause of the SELECT statement.

For example, the following select data from the employee id, first name, last name, and hire date of all rows in the employees table:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    hire_date
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Try It

Now, the result set includes only four columns specified in the SELECT clause:

+-------------+-------------+-------------+------------+
| employee_id | first_name  | last_name   | hire_date  |
+-------------+-------------+-------------+------------+
|         100 | Steven      | King        | 1987-06-17 |
|         101 | Neena       | Kochhar     | 1989-09-21 |
|         102 | Lex         | De Haan     | 1993-01-13 |
|         103 | Alexander   | Hunold      | 1990-01-03 |
|         104 | Bruce       | Ernst       | 1991-05-21 |
|         105 | David       | Austin      | 1997-06-25 |
|         106 | Valli       | Pataballa   | 1998-02-05 |
|         107 | Diana       | Lorentz     | 1999-02-07 |
|         108 | Nancy       | Greenberg   | 1994-08-17 |
|         109 | Daniel      | Faviet      | 1994-08-16 |
|         110 | John        | Chen        | 1997-09-28 |
...
Code language: plaintext (plaintext)

3) SQL SELECT – performing a simple calculation

The following example uses the SELECT statement to get the first name, last name, salary, and new salary:

SELECT 
    first_name, 
    last_name, 
    salary, 
    salary * 1.05
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

The expression salary * 1.05 adds 5% to the salary of every employee. By default, SQL uses the expression as the column heading:

+-------------+-------------+----------+---------------+
| first_name  | last_name   | salary   | salary * 1.05 |
+-------------+-------------+----------+---------------+
| Steven      | King        | 24000.00 |    25200.0000 |
| Neena       | Kochhar     | 17000.00 |    17850.0000 |
| Lex         | De Haan     | 17000.00 |    17850.0000 |
| Alexander   | Hunold      |  9000.00 |     9450.0000 |
| Bruce       | Ernst       |  6000.00 |     6300.0000 |
| David       | Austin      |  4800.00 |     5040.0000 |
| Valli       | Pataballa   |  4800.00 |     5040.0000 |
| Diana       | Lorentz     |  4200.00 |     4410.0000 |
| Nancy       | Greenberg   | 12000.00 |    12600.0000 |
...
Code language: plaintext (plaintext)

To assign an expression or a column an alias, you specify the AS keyword followed by the column alias as follows:

expression AS column_alias
Code language: SQL (Structured Query Language) (sql)

For example, the following SELECT statement uses the new_salary as the column alias for the salary * 1.05 expression:

SELECT 
    first_name, 
    last_name, 
    salary, 
    salary * 1.05 AS new_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+-------------+----------+------------+
| first_name  | last_name   | salary   | new_salary |
+-------------+-------------+----------+------------+
| Steven      | King        | 24000.00 | 25200.0000 |
| Neena       | Kochhar     | 17000.00 | 17850.0000 |
| Lex         | De Haan     | 17000.00 | 17850.0000 |
| Alexander   | Hunold      |  9000.00 |  9450.0000 |
| Bruce       | Ernst       |  6000.00 |  6300.0000 |
| David       | Austin      |  4800.00 |  5040.0000 |
| Valli       | Pataballa   |  4800.00 |  5040.0000 |
| Diana       | Lorentz     |  4200.00 |  4410.0000 |
| Nancy       | Greenberg   | 12000.00 | 12600.0000 |

Summary

• Use the SQL SELECT statment to select data from a table.
• To select data from a table, specify the table name in the FROM clause and a list of column in the SELECT clause.
• The SELECT * is the shorthand of the SELECT all columns in a table.