SQL: SELECT Statement

This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples and practice exercises.

 

Description

The SQL SELECT statement is used to retrieve records from one or more tables in your SQL database. The records retrieved are known as a result set.

 

Syntax

The syntax for the SELECT statement in SQL is:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected. If no conditions are provided, then all records will be selected.

ORDER BY expression

Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default ]behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

DDL/DML for Examples

In this tutorial we will use the tables in SCOTT schema, but some tables are not available there. 

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Select All Fields from a Table

Let's look at an example that shows how to use the SQL SELECT statement to select all fields from a table.

In this example, we have a table called emp with the following data:

empno	ename	job	mgr	hiredate	sale	comm	deptno
7369	SMITH	CLERK	7902	17-DEC-80	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7566	JONES	MANAGER	7839	02-APR-81	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	NULL	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	NULL	10
7788	SCOTT	ANALYST	7566	09-DEC-82	3000	NULL	20
7839	KING	PRESIDENT	NULL	17-NOV-81	5000	NULL	10
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	12-JAN-83	1100	NULL	20
7900	JAMES	CLERK	7698	03-DEC-81	950	NULL	30
7902	FORD	ANALYST	7566	03-DEC-81	3000	NULL	20
7934	MILLER	CLERK	7782	23-JAN-82	1300	NULL	10

Now let's demonstrate how the SELECT statement works by selecting all columns from the EMP table. Enter the following SELECT statement:

SELECT *
FROM emp
WHERE deptno =10
ORDER BY ename ASC;

There will be 3 records selected. These are the results that you should see:

empno	ename	job	mgr	hiredate	sale	comm	deptno
7782	CLARK	MANAGER	7839	09-JUN-81	2450	NULL	10
7839	KING	PRESIDENT	NULL	17-NOV-81	5000	NULL	10
7934	MILLER	CLERK	7782	23-JAN-82	1300	NULL	10

In this example, we've used * to signify that we wish to view all fields from the  EMP table where the  deptno is 10. The result set is sorted by ename in ascending order. 

Example - Select Individual Fields from a Table

You can also use the SQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

Now let's demonstrate how to use the SELECT statement to select individual columns from a table. Enter the following SELECT statement:

SELECT empno, ename, deptno, sal
FROM emp
WHERE sale > 2900
ORDER BY ename ASC;

4 records should be selected. These are the results that you should see:

empno	ename	deptno	sal
7902	FORD	20	3000
7566	JONES	20	2975
7839	KING	10	5000
7788	SCOTT	20	3000

This example would return only the empno, ename, deptno and sal fields from the emp table where the sal value is greater than 2900. The results are sorted by ename in ascending order.

Example - Select Individual Fields From Multiple Tables

You can also use the SQL SELECT statement to retrieve fields from multiple tables.

In this example, we have a table called dept with the following data:

 

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

And a table called emp with the following data:

 

empno	ename	job	mgr	hiredate	sale	comm	deptno
7369	SMITH	CLERK	7902	17-DEC-80	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7566	JONES	MANAGER	7839	02-APR-81	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	NULL	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	NULL	10
7788	SCOTT	ANALYST	7566	09-DEC-82	3000	NULL	20
7839	KING	PRESIDENT	NULL	17-NOV-81	5000	NULL	10
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	12-JAN-83	1100	NULL	20
7900	JAMES	CLERK	7698	03-DEC-81	950	NULL	30
7902	FORD	ANALYST	7566	03-DEC-81	3000	NULL	20
7934	MILLER	CLERK	7782	23-JAN-82	1300	NULL	10

 Now let's select columns from both the emp and dept tables. Enter the following SELECT statement:

SELECT emp.ename, dept.dname, emp.sal
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE emp.sal > 2000
ORDER BY dept.dname, emp.ename;

There will be 6 records selected. These are the results that you should see:

ename	dname	sal
CLARK	ACCOUNTING	2450
KING	ACCOUNTING	5000
FORD	RESEARCH	3000
JONES	RESEARCH	2975
SCOTT	RESEARCH	3000
BLAKE	SALES	2850

This SELECT example joins two tables to gives us a result set that displays the ename from the emp table and the dname from the dept table and sal from emp table. Each time we use a column in the SELECT statement, we prefix the column with the table name (for example, emp.ename) in case there is any ambiguity about which table the column belongs to.

If you wanted to select all fields from the dept table and then the ename field from the emp  table, you enter the following SELECT statement:

SELECT dept.*, emp.ename
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE emp.sal > 2000
ORDER BY dept.dname, emp.ename;

There will be 6 records selected. These are the results that you should see:

 

deptno	dname	loc	ename
10	ACCOUNTING	NEW YORK	CLARK
10	ACCOUNTING	NEW YORK	KING
20	RESEARCH	DALLAS	FORD
20	RESEARCH	DALLAS	JONES
20	RESEARCH	DALLAS	SCOTT
30	SALES	CHICAGO	BLAKE

In this example, we use dept.* to signify that we want to select all fields from the dept table and then we select the ename field from the emp table.