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_listFROM 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 syntaxfor 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.
![How to Use SQL SELECT Statement to Query Data From a Single Table (1) How to Use SQL SELECT Statement to Query Data From a Single Table (1)](https://i0.wp.com/www.sqltutorial.org/wp-content/uploads/2016/03/employees_table.png)
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 | [emailprotected] | 515.123.4567 | 1987-06-17 | 4 | 24000.00 | NULL | 9 || 101 | Neena | Kochhar | [emailprotected] | 515.123.4568 | 1989-09-21 | 5 | 17000.00 | 100 | 9 || 102 | Lex | De Haan | lex.de [emailprotected] | 515.123.4569 | 1993-01-13 | 5 | 17000.00 | 100 | 9 || 103 | Alexander | Hunold | [emailprotected] | 590.423.4567 | 1990-01-03 | 9 | 9000.00 | 102 | 6 || 104 | Bruce | Ernst | [emailprotected] | 590.423.4568 | 1991-05-21 | 9 | 6000.00 | 103 | 6 || 105 | David | Austin | [emailprotected] | 590.423.4569 | 1997-06-25 | 9 | 4800.00 | 103 | 6 || 106 | Valli | Pataballa | [emailprotected] | 590.423.4560 | 1998-02-05 | 9 | 4800.00 | 103 | 6 || 107 | Diana | Lorentz | [emailprotected] | 590.423.5567 | 1999-02-07 | 9 | 4200.00 | 103 | 6 || 108 | Nancy | Greenberg | [emailprotected] | 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_dateFROM 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.05FROM 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_salaryFROM 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 theSELECT
clause. - The
SELECT *
is the shorthand of theSELECT
all columns in a table.
Was this tutorial helpful ?