[Ad-Oracle-Sql] 3. Sub-Queries and Merge Statements

📋 This is my note-taking from what I learned in the class “Advanced Database Concepts”

Objective

• Determine when using a sub-query is appropriate
• Identify which clauses can contain sub-queries
• Distinguish between an outer query and a sub-query
• Use a single-row sub-query in a WHERE clause
• Use a single-row sub-query in a HAVING clause
• Use a single-row sub-query in a SELECT clause
• Distinguish between single-row and multiple-row comparison operators
• Use a multiple-row sub-query in a WHERE clause
• Use a multiple-row sub-query in a HAVING clause
• Use a multiple-column sub-query in a WHERE clause
• Create an inline view using a multiple-column sub-query in a FROM clause
• Compensate for NULL values in sub-queries
• Distinguish between correlated and uncorrelated sub-queries
• Nest a sub-query inside another sub-query
• Use a sub-query in a DML action
• Process multiple DML actions with a MERGE statement

Sub-Queries and Their Uses

• Sub-query: A query nested inside another query
• Used when a query is based on an unknown value
• Requires SELECT and FROM clauses
• Must be enclosed in parentheses
• Place on right side of comparison operator

Types of Sub-Queries

Sub-Query	Description
Single-row sub-query	Returns to the outer query one row of results that consists of one column
Multiple-row sub-query	Returns to the outer query more than one row of results
Multiple-column sub-query	Returns to the outer query more than one column of results
Correlated sub-query	References a column in the outer query, and executes the sub-query once for every row in the outer query
Uncorrelated sub-query	Executes the sub-query first and passes the value to the outer query

Single-Row Sub-Queries

• Can only return one result to the outer query
• Operators include =, >, <, >=, <=, < >

Single-Row Sub-Query in a WHERE Clause

• Used for comparison against individual data

SELECT category, title, cost
  FROM books
  WHERE cost >
        (SELECT cost
        FROM books
        WHERE title = 'DATABASE IMPLEMENTATION')
    AND category = 'COMPUTER';

Single-Row Sub-Query in a HAVING Clause

• Required when returned value is compared to grouped data

SELECT category, AVG(retail-cost) "Average Profit"
  FROM books
  GROUP BY category
  HAVING AVG(retail-cost) > (SELECT AVG(retail-cost)
                              FROM books
                              WHERE category = 'LITERATURE');

Single-Row Sub-Query in a SELECT Clause

• Replicates sub-query value for each row displayed

SELECT title, retail
              (SELECT TO_CHAR(AVG(retail),999.99)
                FROM books) "Overall Average"
  FROM books;

Multiple-Row Sub-Queries

• Return more than one row of results
• Require use of IN, ANY, ALL, or EXISTS operators

ANY and ALL Operators

• Combine with arithmetic operators

Operator	Description
>ALL	More than the highest value returned by the sub-query
<ALL	Less than the lowest value returned by the sub-query
<ANY	Less than the highest value returned by the sub-query
>ANY	More than the lowest value returned by the sub-query
=ANY	Equal to any value returned by the sub-query (same as IN)

Multiple-Row Sub-Query in a WHERE Clause

SELECT title, retail, category
  FROM books
  WHERE retail IN (SELECT MAX(retail)
                    FROM books
                    GROUP BY category)
  ORDER BY category;

SELECT title, retail
  FROM books
  WHERE retail <ANY (SELECT retail
                      FROM books
                      WHERE category = 'COOKING');

Multiple-Row Sub-Query in a HAVING Clause

SELECT order#, SUM(quantity*paideach)
  FROM orderitems
  HAVING SUM(quantity*paideach) >ALL (SELECT SUM(quantity*paideach)
                                      FROM customers JOIN orders USING (customer#)
                                        JOIN orderitems USING (order#)
                                      WHERE state = 'FL'
                                      GROUP BY order#)
  GROUP BY order#;

Multiple-Column Sub-Queries

• Return more than one column in results
• Can return more than one row
• Column list on the left side of operator must be in parentheses
• Use the IN operator for WHERE and HAVING clauses

Multiple-Column Sub-Query in a FROM Clause

• Creates a temporary table

SELECT b.title, b.retail, a.category, a.cataverage
  FROM books b, (SELECT category, AVG(retail) cataverage
                  FROM books
                  GROUP BY category) a
  WHERE b.category = a.category
    AND b.retail > a.cataverage;

Multiple-Column Sub-Query in a WHERE Clause

• Returns multiple columns for evaluation

SELECT title, retail, category
  FROM books
  WHERE (category, retail) IN (SELECT category, MAX(retail)
                                FROM books
                                GROUP BY category)
  ORDER BY category;

NULL Values

• When a sub-query might return NULL values, use NVL function

SELECT customer#
  FROM customers
  WHERE NVL(referred, 0) = (SELECT NVL(referred, 0)
                            FROM customers
                            WHERE customer# = 1005);

Uncorrelated Sub-Queries

• Processing sequence

  • Inner query is executed first

  • Result is passed to outer query

  • Outer query is executed

Correlated Sub-Queries

• Inner query is executed once for each row processed by the outer query
• Inner query references the row contained in the outer query

SELECT title
  FROM books
  WHERE EXISTS (SELECT isbn
                FROM orderitems
                WHERE books.isbn = orderitems.isbn);

Nested Sub-Queries

• Maximum of 255 sub-queries if nested in the WHERE clause
• No limit if nested in the FROM clause

• Innermost sub-query is resolved first, then the next level, etc

• Innermost is resolved first(A), then the second level(B), then the outer query(C)

SELECT customer#, lastname, firstname
  FROM customers JOIN orders USING(customer#)
  WHERE order# IN (SELECT order#
                    FROM orderitems
                    GROUP BY order#
                    HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                      FROM orderitems
                      GROUP BY order#));

Sub-Query Factoring Clause

WITH dcount AS (
  SELECT deptno, COUNT(*) AS dcount
  FROM   employees
  GROUP BY deptno)
SELECT e.lname Emp_Lastname,
       e.deptno e_dept,
       d1.dcount edept_count,
       m.lname manager_name,
       m.deptno mdept,
       d2.dcount mdept_count
FROM   employees e,
       dcount d1,
       employees m,
       dcount d2
WHERE  e.deptno = d1.deptno
AND    e.mgr = m.empno
AND    m.deptno = d2.deptno
    AND e.mgr = '7839';

Sub-Query in a DML Action

UPDATE employees
  SET bonus = (SELECT AVG(bonus)
              FROM employees)
  WHERE empno = 8844;

MERGE Statement

• With a MERGE statement, a series of DML actions can occur with a single SQL statement
• Conditionally updates one data source based on another

MERGE INTO books_1 a
  USING books_2 b
    ON (a.isbn = b.isbn)
  WHEN MATCHED THEN
    UPDATE SET a.retail = b.retail, a.category = b.category
  WHEN NOT MATCHED THEN
    INSERT (isbn, title, pubdate, retail, category)
      VALUES (b.isbn, b.title, b.pubdate, b.retail, b.category);

SELECT * FROM books_1;

The following explains each part of the previous MERGE statement:

• MERGE INTO books_1 a: The BOOKS_1 table is to be changed and a table alias of “a” is assigned to this table
• USING books_2 b: The BOOKS_2 table will provide the data to update and/or insert into BOOKS_1 and a table alias of “b” is assigned to this table
• ON (a.isbn = b.isbn): The rows of the two tables will be joined or matched based on isbn
• WHEN MATCHED THEN: If a row match based on ISBN is discovered, execute the UPDATE action in this clause. The UPDATE action instructs the system to modify only two columns (Retail and Category)
• WHEN NOT MATCHED THEN: If no match is found based on the ISBN (a books exists in BOOKS_2 that is not in BOOKS_1), then perform the INSERT action in this clause

MERGE with WHERE

MERGE INTO books_1 a
  USING books_2 b
    ON (a.isbn = b.isbn)
  WHEN MATCHED THEN
    UPDATE SET a.retail = b.retail, a.category = b.category
    WHERE b.category = 'COMPUTER'
  WHEN NOT MATCHED THEN
    INSERT (isbn, title, pubdate, retail, category)
      VALUES (b.isbn, b.title, b.pubdate, b.retail, b.category)
      WHERE b.category = 'COMPUTER';

SELECT * FROM books_1;

MERGE with DELETE

MERGE INTO books_1 a
  USING books_2 b
    ON (a.isbn = b.isbn)
  WHEN MATCHED THEN
    UPDATE SET a.retail = b.retail, a.category = b.category
    DELETE WHERE (b.retail < 50);

SELECT * FROM books_1;

Summary

• A sub-query is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query

  The sub-query must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum

• Sub-queries are completed first; the result of the sub-query is used as input for the outer query
• A single-row sub-query can return a maximum of one value
• Single-row operators include =, >, <, >=, <=, and <>
• Multiple-row sub-queries return more than one row of results
• Operators that can be used with multiple-row sub-queries include IN, ALL, ANY, and EXISTS
• Multiple-column sub-queries return more than one column to the outer query
• NULL values returned by a multiple-row or multiple-column sub-query will not present a problem if the IN or =ANY operator is used
• Correlated sub-queries reference a column contained in the outer query
• Sub-queries can be nested to a maximum depth of 255 sub-queries in the WHERE clause of the parent query
• With nested sub-queries, the innermost sub-query is executed first, then the next highest level sub-query is executed, and so on, until the outermost query is reached
• A MERGE statement allows multiple DML actions to be conditionally performed while comparing data of two tables

---

Back to Top

See other articles in Category Ad-Oracle-Sql