SQL Language The SELECT Statement
THE STRUCTURED QUERY LANGUAGE: SQL
SELECT:Selects rows and columns from one or more tables. May embedded in other SQLcommands.
The Structured Query Language SQL is the ANSI standard language for manipulating relational databases. It is :
Easy to learn, understand and use by professional programmers and by non-technical experts too.
Assertive or non-procedural.
A complete language to manage the database; it is at the same time a
Query language
Data Definition Language
Data Manipulation Language
Data Control Language
This document explains the Query Language ( SELECT command). It uses the following Oracle sample database.
Table: EMP
EMPNO / ENAME / JOB / MGR# / HIREDATE / SAL / COMM / DEPTNO#7369 / SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 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 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 09-DEC-82 / 3000 / 20
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 0 / 30
7876 / ADAMS / CLERK / 7788 / 12-JAN-83 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
Table: DEPT
DEPTNO / DNAME / LOC10 / ACCOUNTING / NEW YORK
20 / RESEARCH / DALLAS
30 / SALES / CHICAGO
40 / OPERATIONS / BOSTON
Table : SALGRADE
GRADE / LOSAL / HISAL1 / 700 / 1200
2 / 1201 / 1400
3 / 1401 / 2000
4 / 2001 / 3000
5 / 3001 / 9999
DATA RETRIEVAL
The command used to extract (retrieve) data from a table is the SELECT-FROM-WHERE (SFW).
Simplified syntax:
SELECT { col1, col2, . . . . . , coln | * }
FROMtable-name
[WHEREsearch-condition ] ; optional
The SFW command is useful to :
select columns
select rows
join tables
sort the result
use built-in functions(row functions and aggregate functions)
treat the NULL values
connect records in a tree
A complete syntax is given in the last page of the current document.
The SELECT indicates the list of information we want to obtain in the result. This list is a combination of :
Column names separated by commas
SQL built-in functions
Arithmetic expressions
Literal
The FROMindicates the involved (needed) tables to built the result.
The WHERE announces the search condition which can use :
comparison operators < , <= , = , > , >= , !=
logical connectives AND, OR, NOT
belonging operator IN, NOT IN
etc.
EXAMPLE
FOR EACH EMPLOYEE WORKING IN THE DEPARTMENT 10, DISPLAY THE EMPLOYEE NUMBER.
SELECT EMPNO
FROMEMP
WHEREDEPTNO =10;
Remarks
If all columns ( the entire row) of the concerned table are needed in the result then use the asterisk (*) after the select. Thus to display all columns for each employee working in the department 10 we write :
SELECT *
FROMEMP
WHEREDEPTNO =10;
Besides, to list for each employee his number, name and job, we write :
SELECT EMPNO, ENAME , JOB
FROMEMP;
Finally, to list all columns and all rows from the employee table, use the following:
SELECT *
FROMEMP;
When the WHERE clause is absent, the SFW becomes as the algebraic PROJECTION.
Beware! The result of a SFW query may contain redundant tuples (records). To eliminate the redundancy we should use the UNIQUE (or its synonym DISTINCT) option.
Look at the difference. The query
SELECT DEPTNOSELECT UNIQUE DEPTNO
FROM EMP;FROMEMP;
whereas
returns 10 20 10 30 20 10returns 10 20 30
The UNIQUE eliminates redundant rows in the result.
THE COMPARISON OPERATORS
The WHERE clause may use the following operators:
=equal
!=different
greater than
>=greater or equal
less than
<= less or equal
BETWEEN ..AND.. within a scope
[NOT] IN: [not]belong
LIKEresembles
IS NULLundefined value (empty field)
IS NOT NULLNot undefined (defined or known) value
{ =| != | > | < | >= | <= } ANYat least one ...
{ =| != | > | < | >= | <= } ALLall ...(each element)
EXAMPLES
DISPLAY THE NAME, SALARY and COMMISSION FOR EMPLOYEES HAVING A COMMISSION GREATER THAN THE SALARY.
SELECT ENAME, SAL, COMM
FROMEMP
WHERECOMM > SAL;
GET THE NAME AND THE SALARY FOR EMPLOYEES HAVING A SALARY BETWEEN 1200 AND 1300 (limits included).
SELECT ENAME, SAL
FROMEMP
WHERESAL BETWEEN 1200 AND 1300 ;
The IN operator
It enables to compare one value with a set of values.
GET EMPLOYEE NUMBER, NAME AND SALARY FOR ALL 'CLERK' OR 'ANALYST'.
SELECT ENAME, ENAME, JOB, SAL
FROMEMP
WHEREJOB IN ('CLERK', 'ANALYST');
The parentheses are compulsory.
The IN could be replaced by JOB = 'CLERK' OR JOB = 'ANALYST'.
The LIKE operator
This operator is used with strings.
EXAMPLE
GET EMPLOYEES HAVING THEIR NAME START WITH 'M'.
SELECT ENAME
FROMEMP
WHEREENAME LIKE 'M%';
ENAME
------
MARTIN
MILLER
The % means a string of any length ( may be null string). The underline character ( _ ) replaces one character.
EXAMPLE
GET EMPLOYEES HAVING 'A' IN THE THIRD POSITION OF THEIR NAME.
SELECT ENAME
FROMEMP
WHEREENAME LIKE '_ _A%';
ENAME
------
BLAKE
CLARK
ADAMS
SORTING THE RESULT
We can sort the result of a select statement by ending the query with the ORDER BY clause.
SELECTcol1, col8, col3
FROM table-name
WHERE. . . . .
ORDER BYcol8, col3 [DESC]column names
or
ORDERBY 2 , 3 [DESC] position of the column in theSELECT
Each sorting column could have its own order ( ASCending or DESCending). The default is ASC (which is assumed for the col8 above).
ORDER BY is always the last in the query
ASC is the default.
EXAMPLE
GET THE NAME , JOB AND SALARY FOR ALL EMPLOYEES. SORT THE RESULT BY JOB ALPHABETICALLY AND BY SALARY DESCENDING.
ENAMEJOBSAL
SELECTENAME, JOB, SAL------
FROMEMPSCOTTANALYST3000
ORDERBYJOB, SAL DESC;FORDANALYST3000
MILLERCLERK1300
ADAMSCLERK1100
JAMESCLERK 950
SELECTENAME, JOB, SALSMITHCLERK 800
FROMEMPJONESMANAGER2975
ORDERBY2, 3 DESC;BLAKEMANAGER2850
CLARKMANAGER2450 ......
JOININGTABLES
GOALS
Combine rows in order to retrieve associated data coming from many tables. To accomplish a join, we specify the following:
The table names in the FROM clause, separated by commas.
The joining condition in the WHERE clause. Because more than one table is involved, we qualify the column names by preceding them with the appropriate table name, followed by a period.
Syntax
SELECT ......
FROMtable1 , table2
WHERE predicate
The relational algebra requires that the tables to be joined must have two comparable columns (defined on the same domain). With SQL, the two columns must be only of the same data type.
EXAMPLE
GET THE DEPARTMENT LOCALITY WHERE THE EMPLOYEE SMITH WORKS.
SELECT LOC
FROMEMP, DEPT
WHEREENAME = 'SMITH'
ANDEMP.DEPTNO = DEPT.DEPTNO; Join condition
LOC
----------
DALLAS
JOIN PROCESSING
The trivial method - but not the best - is to compute the Cartesian product then to apply a selection on it (since the Cartesian product consists of all possible combinations of the two tables' rows).
Because the Cartesian product costs too much, SQL will process otherwise beginning by reducing the number of rows to be joined.
JOINING A TABLE TO ITSELF
It is sometimes useful to join a given table to itself in the purpose to obtain on the same row some attributes coming from one row and others coming from another row of the same table.
EXAMPLES
GET THE NAME OF THE BOSS OF THE EMPLOYEE 'FORD'.
Since each row of the table EMP has a column MGR (manager number), and since this attribute is defined on the same domain with EMPNO, it will be possible to find for any employee the name of his/her manager. This needs to join the table EMP to itself.
SELECT BOSS.ENAME
FROMEMP , EMP BOSS
WHEREEMP.ENAME= 'FORD'
ANDEMP.MGR = BOSS.EMPNO;
ENAME
------
JONES
This example involves a join of table EMP with itself; the join condition is that the manager number of the selected employee (FORD) is equal to the employee number in another row.
Table EMP appears twice in the FROM clause. To distinguish between the two appearances, we introduce arbitrary the synonym BOSS and use it as a qualifier in the SELECT and WHERE clauses.
FIND ALL EMPLOYEES HAVING A SALARY MORE THAN JONES.
EXTERNAL JOIN
GOAL
The external join -of two tables T1 and T2- is a particular join returning the normal join result augmented by all rows from T1 (respectively T2) those don't match any row in T2 (respectively T1).
HOW ?
The (+) concatenates an empty row (null valued row) to tuples those don't participate to the join.
EXAMPLE
CARPERSON
CNOCOLOR OWNERPNOPNAME
1234Red11Ali
1235Yellow32Hesham
1236White23Salah
1237Green4Hamad
Normal join
DISPLAY THE CAR NUMBERS AND THEIR OWNERS.
SELECT CNO, PNAME
FROMCAR,PERSON
WHEREOWNER = PNO;
CNOPNAME
1234 Ali
1235Salah
1236Hesham
Note that persons without cars will not appear in the result (respectively cars without owners); this occurs because there is no corresponding rows for such records.
External join
DISPLAY ALL CARS OWNED OR NOTyet OWNED.
SELECT CNO, COLOR, PNAME
FROMCAR,PERSON
WHEREOWNER = PNO (+);
CNO / COLOR / PNAME1234 / Red / Ali
1235 / Yellow / Salah
1236 / White / Hesham
1237 / Green
DISPLAY CNO,COLOR,PNAME FOR BOTH OWNERS AND NOT OWNERS OF CARS.
SELECT CNO , COLOR, PNAME
FROMCAR,PERSON
WHEREPNO = OWNER (+);
CNO / COLOR / PNAME1234 / Red / Ali
1235 / Yellow / Salah
1236 / White / Hesham
Hamad
Despite the fact that the person Hamad does not have a car, he exists in the result.
SET OPERATORS
ORACLE offers the following collection of operators working on sets.
Algebraic operators / ORACLE operatorsunion / UNION
intersect / INTERSECT
difference / MINUS
Syntax
SELECT......
FROM......
{ UNION | INTERSECT | MINUS }
SELECT......
FROM......
{ UNION | INTERSECT | MINUS }
SELECT......
FROM......
The UNION operator
EXAMPLE
SELECT EMPNO , ENAME ,SAL, COMM, DEPTNO
FROMEMP
WHERESAL < 1500
UNION
SELECT EMPNO , ENAME ,SAL, COMM, DEPTNO
FROMEMP
WHERECOMM IS NOT NULL;
Result of the first SELECTResult of the second SELECT
EMPNO / ENAME / SAL / COMM / DEPTNO / EMPNO / ENAME / SAL / COMM / DEPTNO7369 / SMITH / 800 / 20 / 7499 / ALLEN / 1600 / 300 / 30
7521 / WARD / 1250 / 500 / 30 / 7521 / WARD / 1250 / 500 / 30
7654 / MARTIN / 1250 / 1400 / 30 / 7654 / MARTIN / 1250 / 1400 / 30
7876 / ADAMS / 1100 / 20 / 7844 / TURNER / 1500 / 0 / 30
7900 / JAMES / 950 / 30
7934 / MILLER / 1300 / 10
unionof the two results
EMPNO / ENAME / SAL / COMM / DEPTNO7369 / SMITH / 800 / 20
7521 / WARD / 1250 / 500 / 30
7654 / MARTIN / 1250 / 1400 / 30
7876 / ADAMS / 1100 / 20
7900 / JAMES / 950 / 30
7934 / MILLER / 1300 / 10
7499 / ALLEN / 1600 / 300 / 30
7844 / TURNER / 1500 / 0 / 30
Rule1: The two SELECTs must have the same number of columns. These columns must match in types.
Rule2:No redundant rows in the result (since the result is a set in mathematics meaning).
Think:
Write down the above query otherwise?
Look at this example:
SELECT ENAME NAME, 'SAL' TYPE, SAL AMOUNT
FROMEMP
WHERECOMM IS NOT NULL
UNION
SELECT ENAME ,'COMM' , COMM
FROMEMP
WHERECOMM IS NOT NULL;
Note that the UNION is used to split on many rows data coming from the same record.
NAME / TYPE / AMOUNT------/ ------/ ------
ALLEN / COMM / 300
ALLEN / SAL / 1600
MARTIN / COMM / 1400
MARTIN / SAL / 1250
TURNER / COMM / 0
TURNER / SAL / 1500
WARD / COMM / 500
WARD / SAL / 1250
The MINUS operator
EXAMPLE
SELECT DEPTNO
FROMDEPT
MINUS
SELECT DEPTNO
FROMEMP;
T1 : result of the first SELECTT2 : result of the second SELECT
DEPTNO / DEPTNO10 / 10
20 / 20
30 / 30
40
T1 minus T2
DEPTNO40
Think
What would be the result of the MINUS in the reverse order ( T2 minus T1)?
The INTERSECT operator
EXAMPLES
SELECT EMPNO , ENAME ,SAL, COMM, DEPTNO
FROMEMP
WHERESAL < 1500
INTERSECT
SELECT EMPNO , ENAME ,SAL, COMM, DEPTNO
FROMEMP
WHERECOMM IS NOT NULL;
Result of the first SELECTResult of the second SELECT
EMPNO / ENAME / SAL / COMM / DEPTNO / EMPNO / ENAME / SAL / COMM / DEPTNO7369 / SMITH / 800 / 20 / 7499 / ALLEN / 1600 / 300 / 30
7521 / WARD / 1250 / 500 / 30 / 7521 / WARD / 1250 / 500 / 30
7654 / MARTIN / 1250 / 1400 / 30 / 7654 / MARTIN / 1250 / 1400 / 30
7876 / ADAMS / 1100 / 20 / 7844 / TURNER / 1500 / 0 / 30
7900 / JAMES / 950 / 30
7934 / MILLER / 1300 / 10
intersectof the two results
EMPNO / ENAME / SAL / COMM / DEPTNO7521 / WARD / 1250 / 500 / 30
7654 / MARTIN / 1250 / 1400 / 30
SELECT ENAME , JOB, SAL
FROM EMP
WHERE JOB ='MANAGER'
INTERSECT
SELECT ENAME , JOB, SAL
FROM EMP
WHERE SAL >= 2000;
ENAME / JOB / SALBLAKE / MANAGER / 2850
CLARK / MANAGER / 2450
JONES / MANAGER / 2975
Think
Could you write this query without using the INTERSECT operator ?
NESTED QUERIES
Nested queries are queries within queries. This occurs when the WHERE clause of a given SFW block contains itself a sub query (SFW command ).
The sub query ( or inner query/block) :
oMay return only one column.
ois able to return data from one row or several.
ois typical coded as part of a predicate containing =, which is used when it returns a single value, or IN when it returnsseveral values.
ooften can be used as a substitute for a join when we want to extract columns from a singletable.
We distinguish two kinds of nested queries :
osimple nested queries : the inner query is independent - from the outer query - in the sense that it is completely processed before using its result within the outer block.
ocorrelated (synchronized) queries: the inner query and the outer one have an inter-block reference so that the inner subselect must be processed more than once.
General form
SELECT ......
FROMtable-name
WHEREcol operator ( SELECT.....
FROMtable-name
WHERE...);
Sub query empty error.
SIMPLE NESTED QUERIES
EXAMPLE
DISPLAY NAME , SALARY , JOB FOR EMPLOYEES HAVING A SALARY > JONES ' SALARY.
SELECT ENAME , SAL , JOBouter query
FROMEMP
WHERESAL(SELECT SALinner query
FROMEMP (sub query)
WHERE ENAME = 'JONES' );
ENAME / SAL / JOB------/ ------/ ------
SCOTT / 3000 / ANALYST
KING / 5000 / PRESIDENT
FORD / 3000 / ANALYST
Sub query processing
Note that :
oThe sub query is independent with the outer query.
oOracle ( RDBMS) evaluates the whole sub query, then the outer query using the obtained result.
Think
Could you write the query otherwise (without nested blocks) ?
Sub queries RETURNING MANY ROWS
When the sub query may return more than one row, we must use the operators able to compare one value to a set of values:
IN
{ = | != | > | >= | < | <= | } { ANY | ALL }
asexpression < ANY sub query
or expression < ALL sub query
When is used with ALL, it means the sub query will return several values, and the expression's value should be smaller than all of those values.
If the statement is of the form expression <ANY sub query, the value of the expression should be less than at least one of the returned values. The other operators ( >, = , ... ) work in a similar manner.
EXAMPLE
DISPLAY NAME , SAL FOR EMPLOYEES EARNING MORE THAN ALL EMPLOYEES IN THE DEPT. 30.
SELECT ENAME, SAL
FROMEMP
WHERESAL > ALL (SELECTSAL
FROMEMP
WHEREDEPTNO =30);
ENAME / SAL------/ ------
JONES / 2975
SCOTT / 3000
KING / 5000
FORD / 3000
DISPLAY NAME , JOB FOR EMPLOYEES IN DEPT 10 HAVING THE SAME JOB AS AN EMPLOYEE (anyone) OF THE 'SALES' DEPT.
MULTILEVEL NESTED QUERIES
EXAMPLE
DISPLAY NAME , JOB, SAL FOR EMPLOYEES HAVING THE SAME JOB AS 'JONES' OR EARNING MORE THAN 'FORD'.
Result should be sorted on job and sal.
Think: Could you express the same query without using nested blocks?
CORRELATED SUB QUERIES
For all the sub queries we have examined thus far, ORACLE(RDBMS) evaluated the innermost (last) query first. The evaluation of the sub query returned a single value ( or a set of values). It then substituted this value into the outer level's predicate. Only then did oracle use those results to evaluate the next query.
There are times, however, when this isn't desirable - situations where the inner query depends on a value or values determined by an outer query. In this case, the outer query is repeatedly evaluated and a value determined. Then the inner query is evaluated based on the previously determined value. This type of query is called correlated sub query.
EXAMPLE
LIST OF EMPLOYEES ( ENAME) WORKING IN A DEPT. OTHER THAN THE DEPT. OF THEIR MANAGER.
SELECTENAME
FROMEMP X correlation name
WHERE DEPTNO != (SELECTDEPTNO
FROMEMP
WHEREX.MGR = EMPNO )
AND MGR IS NOT NULL;
ENAME
------
JONES
BLAKE
Necessary to use correlation name for the table.
CORRELATED Sub query processing
Think: Could you write this query differently? (without synchronized queries).
The EXISTS operator
The EXISTS operator is useful to test for the existence of rows in a table that match some specified condition. Such a test is usually performed within a sub query and is connected to the outer query by using the syntax below:
SELECT ....
FROM.....
WHERE [ NOT] EXISTS ( SELECT...
FROM ....
WHERE ...);
EXISTS here represents the existential quantifier. The expression "EXISTS (SELECT ...)" evaluates to true if and only if the result of evaluating its "SELECT..." is not empty (at least one record returned by the sub query following EXISTS).
NOT EXISTS is the opposite, and then evaluates to true if the result of the sub query is empty.
EXISTSis not empty
NOT EXISTSis empty
EXAMPLE
DISPLAY EMPLOYEES WORKING IN DEPTs. HAVING AT LEAST ONE "ANALYST".
SELECT *
FROMEMP E
WHEREEXISTS ( SELECT *
FROMEMP
WHEREJOB = 'ANALYST'
AND E.DEPTNO = DEPTNO );
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790217-DEC-80800.0020
7566JONESMANAGER783902-APR-812975.0020
7788SCOTTANALYST756609-DEC-823000.0020
7876ADAMSCLERK778812-JAN-831100.0020
7902FORDANALYST756603-DEC-813000.0020
For each employee E, the sub query finds a set of all ANALYSTs working in the same department as the employee E. Since the set is not empty then EXISTS evaluates to true and the employee E is displayed.
Think:
Could you write the query without using the EXISTS operator ? Try to use IN.
DISPLAY DEPARTMENTs WITHOUT EMPLOYEES.
SELECT*
FROMDEPT
WHERE NOT EXISTS (SELECT*
FROMEMP
WHERE DEPT.DEPTNO = DEPTNO );
Think:
This query could be formulated twice differently using :
- MINUS