SQL SELECT Statement

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 SELECT statement allows you to retrieve data from one or more tables.

Here’s the basic syntax of the SELECT statement that retrieves data from a single table:

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

In this syntax:

First, specify a comma-separated list of columns from the table you want to retrieve data in the SELECT clause.
Then, provide 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.

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

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

If you want to retrieve data from all the columns of the table, you can list all the columns in the SELECT clause like this:

SELECT
  column1,
  column2,
  column3
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

Alternatively, you can use the star (*) operator as the shorthand for all columns:

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

Since SQL is case-insensitive, you can write SQL keywords such as SELECT and FROM in uppercase, lowercase, camelcase, etc. For example:

select * from table_name;Code language: SQL (Structured Query Language) (sql)

By convention, we’ll use write SQL keywords in uppercase and identifiers such as table names in lowercase. This practice makes SQL statements easier to read.

SQL SELECT statement examples #

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

Selecting data from all columns example #

The following example uses the SELECT statement to retrieve data from all the columns of the employees table:

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

Try It

Output:

 employee_id | first_name  |  last_name  |               email               | phone_number | hire_date  | job_id |  salary  | manager_id | department_id
-------------+-------------+-------------+-----------------------------------+--------------+------------+--------+----------+------------+---------------
         100 | Steven      | King        | [email protected]       | 515.123.4567 | 1987-06-17 |      4 | 24000.00 |       NULL |             9
         101 | Neena       | Kochhar     | [email protected]     | 515.123.4568 | 1989-09-21 |      5 | 17000.00 |        100 |             9
         102 | Lex         | De Haan     | lex.de [email protected]       | 515.123.4569 | 1993-01-13 |      5 | 17000.00 |        100 |             9
         103 | Alexander   | Hunold      | [email protected]  | 590.423.4567 | 1990-01-03 |      9 |  9000.00 |        102 |             6
         104 | Bruce       | Ernst       | [email protected]       | 590.423.4568 | 1991-05-21 |      9 |  6000.00 |        103 |             6
...
Code language: plaintext (plaintext)

The result of the SELECT statement is called a result set.

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 reasons:

The select * returns data from all columns of a table. The application often needs data from one or some columns, not all the columns.
If you use the select *, the database system 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.

Selecting data from specific columns #

To retrieve data from specific columns of a table, you can specify the column list in the SELECT clause.

For example, the following statement retrieves data from the employee_id, first_name, last_name, and hire_date columns in the employees table:

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

Try It

The result set includes only four specified columns:

 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
...Code language: plaintext (plaintext)

Performing calculations #

The following example uses the SELECT statement to retrieve 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)

Try it

The expression salary * 1.05 adds 5% to the salary of every employee.

When you have an expression that involves table columns, you create a calculated column.

The database system may assign a temporary name to the calculated column. For example, MySQL assigns the expression as the column name, while PostgreSQL uses ?column? as the column name of the calculated column.

Here’s the output of PostgreSQL:

 first_name  |  last_name  |  salary  |  ?column?
-------------+-------------+----------+------------
 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
...Code language: plaintext (plaintext)

Column aliases #

A column alias is a temporary name you assign to a column during the execution of a query.

Here’s the syntax for using a column alias:

expression AS column_aliasCode language: SQL (Structured Query Language) (sql)

In this syntax, the AS keyword is optional. Therefore, you can omit it to make the query more concise:

expression column_aliasCode language: SQL (Structured Query Language) (sql)

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

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

Try it

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

Summary #

Use the SELECT statement to retrieve data from a table.
Use the SELECT star to retrieve data from all columns of a table.
A column alias is a temporary name of a column in a query.

Databases #

Was this tutorial helpful ?