Advanced SQL Tutorial (ctd)

This is the 2nd part of our advanced SQL tutorial. Part1 introduced sub-queries and looked at non-correlated sub-queries. This part looks at the uses of sub queries.

There is also some consideration of performance issues, but this aspect is more thoroughly explored in our series on Oracle performance tuning. See also our beginner's SQL tutorial, the PL/SQL tutorials and the other Oracle tutorials

Uses of Sub Queries

The most common use of sub queries is in the WHERE clause of queries to define the limiting condition for the rows returned

(i.e. what value(s) the rows must have to be of interest), as in the above example. However, they can also be used in other parts of the query. Specifically, sub queries can be used:

to define the limiting conditions for SELECT, UPDATE and DELETE statements in the following clauses:-
- WHERE
- HAVING
- START WITH
Instead of a table name in
- INSERT statements
- UPDATE statements
- DELETE
- the FROM clause of SELECT statements
To define the set of rows to be created in the target table of a CREATE TABLE AS or INSERT INTO sql statement.
To define the set of rows to be included by a view or a snapshot in a CREATE VIEW or CREATE SNAPSHOT statement.
To provide the new values for the specified columns in an UPDATE statement

The first example of sub query in SQL shown above, used a simple equality expression as we were interested in only one row, but we can also use the sub query to provide a set of rows.

For example, to find the names of all employees in the same departments as Smith and Jones, we could use the following SQL statement :-

SELECT name FROM emp WHERE dept_no IN
(SELECT dept_no FROM emp WHERE name = 'JONES' OR name = 'SMITH')

In fact, the original example could also return more than one row from the sub query if there were two or more people that were called Jones working in different departments. In the first example a run-time SQL error would be generated in that case, because the first example, by using '=', specified that the sub query should produce no more than one row (it is perfectly legitimate for a sub query to return no rows).

We can reverse the question to ask for the names of all the employees that are NOT in the same department as Jones, To do this, the sense of the sub query just has to be reversed by prefixing it with 'NOT' or '!'. Again depending on whether there might be more than one Jones, we would either use 'IN' or '='

SELECT name FROM emp WHERE dept_no NOT IN
( SELECT dept_no FROM emp WHERE name = 'JONES')

SELECT name FROM emp WHERE dept_no !=
( SELECT dept_no FROM emp WHERE name = 'JONES')

Click on the link to continue this Advanced SQL tutorial

---------------------------------------

Take a short cut to learning SQL and subscribe to our ezine. It's jam-packed full of tips and tricks to help you make your Oracle applications better, faster and smarter. Sign up today and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.