• Single Row sub queries (zero to one rows returned)
• Multi-Row sub queries (one or more rows returned)
• Multi-Column sub queries (one than more column)
• Correlated sub queries (Inner query references the outer query)
• Set Operations (Union, Union All, Intersect, Minus)
• Conditional Operations (DECODE, TRANSLATE, CASE)

 

Single Row Queries in the WHERE Clause (Most commin):

SELECT first_name, last_name FROM customers
WHERE customer_id = (SELECT customer_id FROM customers WHERE last_name = 'Brown');

 

SELECT product_id, name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

 

Sub queries in the HAVING Clause:

SELECT product_type_id, AVG(price) FROM products
    GROUP BY product_type_id
    HAVING AVG(price) < (SELECT MAX(AVG(price)) FROM products
                                            GROUP BY product_type_id);

 

Sub queries in the FROM Clause:

SELECT product_id
    FROM (SELECT product_id FROM products WHERE product_id < 3);

 

SELECT prds.product_id, price, purchases_data.product_count
    FROM products prds, (SELECT product_id, COUNT(product_id) product_count FROM purchases
                                                GROUP BY product_id) purchases_data
WHERE prds.product_id = purchases_data.product_id;

 

NOTE:  Sub Queries may not contain an ORDER BY clause.  That is partly because they are not necessary since they are never displayed data.  The outer most query may have an ORDER BY.

SELECT product_id, name, price FROM products
      WHERE price > (SELECT AVG(price) FROM products)
        ORDER BY product_id DESC;

 

Multiple Row Sub Queries:

SELECT product_id, name FROM products
    WHERE product_id IN (SELECT product_id FROM products WHERE name LIKE '%e%');

SELECT product_id, name FROM products
    WHERE product_id NOT IN (SELECT product_id FROM purchases);

 

ALL AND ANY:

The ALL and ANY operators can be used with subqueries to produce a single column of numbers. If the subquery is preceded by the ALL operator, the condition is true only if it satisfies all values produced by the subquery. If the subquery is preceded by the ANY operator, the condition is true if it satisfies any value (one or more) produced by the subquery.

SELECT employee_id, last_name FROM employees
    WHERE salary < ANY (SELECT low_salary FROM salary_grades);

SELECT employee_id, last_name FROM employees
    WHERE salary > ALL (SELECT high_salary FROM salary_grades);

 

Multiple Column Sub Queries:

SELECT product_id, product_type_id, name, price FROM products
    WHERE (product_type_id, price) IN (SELECT product_type_id, MIN(price) FROM products
                                                                    GROUP BY product_type_id);

 

Correlated Sub Queries:

SELECT product_id, product_type_id, name, price FROM products outer
    WHERE price > (SELECT AVG(price) FROM products inner
                                    WHERE inner.product_type_id = outer.product_type_id);

 

Using EXIST and NOT EXISTS:
Sometimes you just want to pull all rows from one table, so long as it has [or doesn't have] corresponding rows in another table, the [NOT] EXIST command is another way to do this.

Example:

SELECT employee_id, last_name
FROM employees outer
WHERE EXISTS
   (SELECT employee_id FROM employees inner
    WHERE inner.manager_id = outer.employee_id);


Output:


EMPLOYEE_ID  LAST_NAME
------------ ---------
           1 Smith
           2 Johnson

SELECT employee_id, last_name
FROM employees outer
WHERE EXISTS
   (SELECT 1  FROM employees inner
    WHERE inner.manager_id = outer.employee_id);
 

SELECT product_id, name
FROM products outer
WHERE NOT EXISTS
  (SELECT 1 FROM purchases inner
	WHERE inner.product_id = outer.product_id);
  
PRODUCT_ID NAME
---------- ------------------------------
         4 Tank War
         5 Z Files
         6 2412: The Return
         7 Space Force 9
         8 From Another Planet
         9 Classical Music
        10 Pop 3
        11 Creative Yell
        12 My Front Line

 

NOTE:  It is generally faster to use EXISTS vs. IN and JOIN because EXIST only looks for a single match (existence) where as IN or JOIN checks each value.

SELECT product_type_id, name FROM product_types outer
    WHERE NOT EXISTS
            (SELECT 1 FROM products inner WHERE inner.product_type_id = outer.product_type_id);

PRODUCT_TYPE_ID  NAME
--------------- ----------
              5 Magazine

SELECT product_type_id, name FROM product_types outer
    WHERE product_type_id NOT IN
            (SELECT product_type_id FROM products);

no rows selected -- because of the null in product_type

SELECT product_type_id, name FROM product_types outer
    WHERE product_type_id NOT IN
            (SELECT NVL(product_type_id,0) FROM products);

You can use a nested subquery within a subquery, to find data using one SQL statement.

SELECT product_type_id, AVG(price)
FROM products 
GROUP BY product_type_id
HAVING AVG(price) < (
	SELECT MAX(AVG(price))
	FROM products 
	WHERE product_id IN (
		SELECT product_id 
		FROM purchases
		WHERE quantity > 1)
        GROUP BY product_type_id);


Output:

PRODUCT_TYPE_ID AVG(PRICE)
--------------- ---------
                13.49
              4 13.99
              3 13.24

As you would expect, the queries are evaluated from the innermost query to the outermost query. The query in this example is evaluated in three steps.

1. The innermost query is evaluated first producing a temporary table of product id's for those products with a quantity greater than 1.
2. The next sub-query is evaluated, producing a second temporary table with a list of MAX(AVG(price)) for each product_type_id.
3. The outer query is evaluated last, producing the desired list of product id's and average prices. Only those products whose id's are in the temporary table produced in Step 2 are included in the result.

UPDATE and DELETE statements will be covered in subsequent chapters, however you can also use them with SUB Queries:

UPDATE employees 
	SET salary = (SELECT AVG(high_salary) FROM salary_grades)
	WHERE employee_id = 4;

UPDATE customers
	SET (city, state) = (SELECT city, state FROM cities 
				WHERE cities.zip = customers.zip);

DELETE FROM employees
	WHERE salary > (SELECT AVG(high_salary) FROM salary_grades);

SET OPERATIONS:
In SQL, you can use the normal set operations: union, intersection, and difference. The union of two tables is a table containing every row that is in either the first table of in the second table or both. The intersection (intersect) of two tables is a table containing every row that is in both tables. The difference (minus) of two tables is the set of every row that is in the first table but not in the second table.

SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name IN ('White', 'Black')
UNION
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name LIKE 'Bl%';

CUSTOMER_ID LAST_NAME                 FIRST_NAME
----------- -------------------- ---------------
          3 White                Steve
          4 Black                Gail
          5 Blue                 Doreen
 

SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name IN ('White', 'Black')
UNION ALL
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name LIKE 'Bl%';

CUSTOMER_ID LAST_NAME                 FIRST_NAME
----------- -------------------- ---------------
          3 White                Steve
          4 Black                Gail
          4 Black                Gail
          5 Blue                 Doreen
 

The two queries in a union must have the same structure. That is, they must be union-compatible. Two queries are union-compatible if they have the same number of columns and if their coreesponding columns have identical data types and lengths.

Note: The definitions of union-compatible does not state that the columns of the two queries must be identical, but rather that the columns must be of the same type and length. Thus, if one column is CHAR(20), the matching column in the second table must also be CHAR(20).

SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name IN ('White', 'Black')
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name LIKE 'Bl%';

CUSTOMER_ID LAST_NAME                 FIRST_NAME
----------- -------------------- ---------------
          4 Black                Gail

SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name IN ('White', 'Black')
MINUS
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name LIKE 'Bl%';

CUSTOMER_ID LAST_NAME                 FIRST_NAME
----------- -------------------- ---------------
          3 White                Steve

TRANSLATE (char, from, to):  char with all characters in from translated to the corresponding character in to.

          Example:

SELECT name, TRANSLATE(name, 'abcdefghi', 'IHGFEDCBA') As Encrypted FROM products;

            OutPut:

NAME                           ENCRYPTED
------------------------------ ------------------------------
Modern Science                 MoFErn SGAEnGE
Chemistry                      CBEmAstry
Supernova                      SupErnovI
Tank War                       TInk WIr
Z Files                        Z FAlEs
2412: The Return               2412: TBE REturn
Space Force 9                  SpIGE ForGE 9
From Another Planet            From AnotBEr PlInEt
Classical Music                ClIssAGIl MusAG
Pop 3                          Pop 3
Creative Yell                  CrEItAvE YEll

NAME                           ENCRYPTED
------------------------------ ------------------------------
My Front Line                  My Front LAnE

12 rows selected.

DECODE  - the equivalent of if, then else in SQL

Syntax:

DECODE (expression, search, result, default)

Example:

SELECT name, DECODE(product_type_id,
                1, 'Text Book',
                2, 'Video DVD',
                3, 'Video Game',
                4, 'Audio CD', 'NoneSpecified') Type
FROM products;

         Yields:

NAME                           TYPE
------------------------------ -------------
Modern Science                 Text Book
Chemistry                      Text Book
Supernova                      Video DVD
Tank War                       Video DVD
Z Files                        Video DVD
2412: The Return               Video DVD
Space Force 9                  Video Game
From Another Planet            Video Game
Classical Music                Audio
Pop 3                          Audio
Creative Yell                  Audio

NAME                           TYPE
------------------------------ -------------
My Front Line                  NoneSpecified

12 rows selected.

CASE  - the equivalent of DECODE

Syntax:

CASE
    WHEN condition1 THEN result1

Example:

SELECT name,
    CASE product_type_id
        WHEN 1 THEN 'Text Book'
         WHEN  2 THEN 'Video DVD'
         WHEN 3 THEN 'Video Game'
        WHEN 4 THEN 'Audio CD'
        ELSE 'NoneSpecified'
    END AS Type
FROM products;

         Yields:

NAME                           TYPE
------------------------------ -------------
Modern Science                 Text Book
Chemistry                      Text Book
Supernova                      Video DVD
Tank War                       Video DVD
Z Files                        Video DVD
2412: The Return               Video DVD
Space Force 9                  Video Game
From Another Planet            Video Game
Classical Music                Audio
Pop 3                          Audio
Creative Yell                  Audio

NAME                           TYPE
------------------------------ -------------
My Front Line                  NoneSpecified

12 rows selected.