The WHERE clause matches expressions using conditional comparisons, to be covered shortly. In addition logical operators (AND, OR and NOT) can be used to include multiple conditions in a WHERE clause.
SELECT { [alias.]column | expression | [alias.]* [ , … ] }
FROM [schema.]table [alias]
[ WHERE [ [schema.]table.|alias.] { column | expression }
comparison { … }
[ { AND | OR } [ NOT ] … ]
];
Let's look at the Subject table.
SELECT * FROM subject;
SUBJECT_ID PARENT_ID NAME
---------- ---------- --------------------------------
1 Non-Fiction
2 1 Self Help
3 1 Esoteric
4 1 Metaphysics
5 1 Computers
6 Fiction
7 6 Science Fiction
8 6 Fantasy
9 6 Drama
10 6 Whodunnit
11 6 Suspense
12 6 Literature
13 12 Poetry
14 12 Victorian
15 12 Shakespearian
16 12 Modern American
17 12 19th Century American
21 6 Subject name
18 rows selected.
SELECT * FROM subject WHERE subject_id = 1;
SUBJECT_ID PARENT_ID NAME
---------- ---------- --------------------------------
1 Non-Fiction
Logical operators (AND, OR and NOT) can be used to include multiple conditions in a WHERE clause.
SELECT … FROM … WHERE … AND … OR ( … AND … );
Note the application of precedence using the parentheses around the OR logical operator in the following example. The AND logical operator has higher precendence than the OR operator. What does this mean in English? AND is evaluated before OR, regardless of order. Any expression enclosed in parentheses is executed with higher precedence than individual logical operators.
SELECT * FROM edition
WHERE (format = 'Paperback' OR format = 'Hardcover')
AND print_date IS NOT NULL;
ISBN PUBLISHER_ID PUBLICATION_ID PRINT_DAT PAGES LIST_PRICE FORMAT
---------- ------------ -------------- --------- ---------- ---------- -----------
246118318 10 3 28-APR-83 234 9.44 Hardcover
345334787 8 3 31-DEC-85 4.99 Paperback
449208133 11 9 31-MAY-85 640 6.99 Paperback
345323440 8 10 31-JUL-96 608 7.99 Paperback
345333926 2 11 30-NOV-90 352 6.99 Paperback
In the following queries the fourth example will return no rows since a SUBJECT_ID cannot be two different values at the same time.
SELECT * FROM subject WHERE subject_id = 1 AND parent_id IS NULL; SELECT * FROM subject WHERE subject_id = 1 OR parent_id IS NULL; SELECT * FROM subject WHERE subject_id = 1 OR subject_id = 2; SELECT * FROM subject WHERE subject_id = 1 AND subject_id = 2; SELECT subject_id, parent_id FROM subject WHERE parent_id IS NULL AND (subject_id = 1 OR subject_id = 2);
The following are valid conditional comparisons in Oracle.
Once again let's use the Subject table.
SELECT * FROM subject;
SUBJECT_ID PARENT_ID NAME
---------- ---------- --------------------------------
1 Non-Fiction
2 1 Self Help
3 1 Esoteric
4 1 Metaphysics
5 1 Computers
6 Fiction
7 6 Science Fiction
8 6 Fantasy
9 6 Drama
10 6 Whodunnit
11 6 Suspense
12 6 Literature
13 12 Poetry
14 12 Victorian
15 12 Shakespearian
16 12 Modern American
17 12 19th Century American
21 6 Subject name
18 rows selected.
SELECT * FROM subject WHERE subject_id = 1;
SUBJECT_ID PARENT_ID NAME
---------- ---------- --------------------------------
1 Non-Fiction
SELECT * FROM subject WHERE subject_id != 1;
SUBJECT_ID PARENT_ID NAME
---------- ---------- ----------------------------
2 1 Self Help
3 1 Esoteric
4 1 Metaphysics
5 1 Computers
6 Fiction
7 6 Science Fiction
8 6 Fantasy
9 6 Drama
10 6 Whodunnit
11 6 Suspense
12 6 Literature
13 12 Poetry
14 12 Victorian
15 12 Shakespearian
16 12 Modern American
17 12 19th Century American
21 6 Subject name
17 rows selected.
SELECT * FROM subject WHERE subject_id < 10;
SUBJECT_ID PARENT_ID NAME
---------- ---------- --------------------------------
1 Non-Fiction
2 1 Self Help
3 1 Esoteric
4 1 Metaphysics
5 1 Computers
6 Fiction
7 6 Science Fiction
8 6 Fantasy
9 6 Drama
9 rows selected.
SELECT * FROM subject WHERE subject_id >= 30;
no rows selected
SELECT name FROM author WHERE name LIKE '%a%'; SELECT name FROM author WHERE name LIKE 'A%';
Combining with logical operators.
SELECT name FROM author WHERE name LIKE 'A%' OR name LIKE 'B%'; SELECT name FROM author WHERE name LIKE 'A%' AND name LIKE 'B%';
Combining with logical operators and equality.
SELECT author_id, name FROM author WHERE name LIKE 'A%' OR name LIKE 'B%' AND author_id <= 10; SELECT author_id, name FROM author WHERE (name LIKE 'A%' OR name LIKE 'B%') AND author_id <= 10; SELECT author_id, name FROM author WHERE (name LIKE 'A%' OR name LIKE 'B%') AND NOT author_id <= 10;
SELECT author_id FROM author WHERE author_id IN (1,2,3,4,5); SELECT author_id FROM author WHERE author_id NOT IN (1,2,3,4,5);
This query finds everything because the response from DUAL always exists.
SELECT author_id FROM author WHERE EXISTS (SELECT * FROM DUAL);
This query also finds everything by joining the Author table to itself.
SELECT a.author_id FROM author a WHERE EXISTS (SELECT author_id FROM author WHERE author_id < 10 AND author_id = a.author_id);
This query finds every Publication with a Co-Author.
SELECT * FROM publication p WHERE EXISTS
(SELECT publication_id FROM coauthor
WHERE publication_id = p.publication_id);
PUBLICATION_ID SUBJECT_ID AUTHOR_ID TITLE
-------------- ---------- ---------- ---------------------
9 7 4 Lucifer's Hammer
10 7 4 Footfall
SELECT subject_id FROM subject WHERE subject_id BETWEEN 1 AND 10;
ANY allows any rows within the set. SOME is the same as ANY. ALL implies every set element must be present and thus the third query following will find no rows at all.
SELECT subject_id FROM subject WHERE subject_id = ANY(1,2,3,4,5); SELECT subject_id FROM subject WHERE subject_id = SOME(1,2,3,4,5); SELECT subject_id FROM subject WHERE subject_id = ALL(1,2,3,4,5);
SELECT { [alias.]column | expression | [alias.]* [ , … ] }
FROM [schema.]table [alias]
[ WHERE [ [schema.]table.|alias.] { column | expression }
comparison { … }
[ { AND | OR } [ NOT ] … ]
]
[ ORDER BY
{ { column | expression | position }
[ ASC | DESC ] [ NULLS { FIRST | LAST } ]
[, … ] }
];
ASC is the default for the ORDER BY clause.
SELECT name FROM subject ORDER BY name; SELECT name FROM subject ORDER BY name ASC;
SELECT name FROM subject ORDER BY name DESC;
SELECT print_date FROM edition ORDER BY print_date NULLS FIRST;
SELECT print_date FROM edition ORDER BY print_date DESC NULLS LAST;
SELECT a.*, p.* FROM author a, publication p ORDER BY a.name, p.title;
The position of the column on the SELECT column clause dictates the order of the output.
SELECT author_id, name FROM author ORDER BY 1, 2;
AUTHOR_ID NAME
---------- --------------------------------
1 Orson Scott Card
2 James Blish
3 Isaac Azimov
4 Larry Niven
5 Jerry Pournelle
6 William Shakespeare
6 rows selected.
SELECT author_id, name FROM author ORDER BY 2, 1;
AUTHOR_ID NAME
---------- --------------------------------
3 Isaac Azimov
2 James Blish
5 Jerry Pournelle
4 Larry Niven
1 Orson Scott Card
6 William Shakespeare
6 rows selected.
This example sorts using the SUBSTR function expression.
SELECT subject_id, name AS Subject, SUBSTR(name,2,5) AS ShortName
FROM subject
ORDER BY SUBSTR(name,2,5);
SUBJECT_ID SUBJECT SHORT
---------- -------------------------------- -----
17 19th Century American 9th C
8 Fantasy antas
7 Science Fiction cienc
2 Self Help elf H
4 Metaphysics etaph
15 Shakespearian hakes
10 Whodunnit hodun
6 Fiction ictio
14 Victorian ictor
12 Literature itera
16 Modern American odern
13 Poetry oetry
5 Computers omput
1 Non-Fiction on-Fi
9 Drama rama
3 Esoteric soter
21 Subject name ubjec
11 Suspense uspen
18 rows selected.