DB2 - SQL Correlated Subquery

In correlated subquery, the inner query does not work independently of the outer query.

In this, the inner query is performed once for each row of the outer query.

To correlate the table in the inner query with the table in the outer query, you need to define an alias for the outer query and use it as a qualifier in the inner query.

1. When you use the alias in this context, it is called “correlation name” and the connection it makes is called a “correlated reference”.

2. A correlated subquery with the EXISTS keyword does not name any column because no data is transferred when you use EXISTS.

Example:

SELECT cust_name
    FROM tb_cust A
      WHERE NOT EXISTS
      (SELECT * FROM tb_inv WHERE inv_cust = A.cust_no)

EXISTS Operator

• The EXISTS operator is used for correlated subqueries.

• It tests if the subquery returns at least one row.

• The EXISTS operator returns true or false, never unknown.

• Because EXISTS tests only if a row exists, the columns shown in the SELECT list of the subquery are irrelevant. Typically, you use a single character text literal such as '1' or 'X' or the keyword NULL.

Example:

This is a correlated subquery displays instructors where the INSTRUCTOR_ID has a matching row in the SECTION table.

The result shows the INSTRUCTOR_ID, INSTRUCTOR_FIRST_NAME column values of instructors assigned to at least one section.

SELECT instructor_id, instructor_last_name
    FROM tb_instructor I
      WHERE EXISTS
      (SELECT 'X'
          FROM tb_section
            WHERE I.instructor_id = instructor_id)

For every row of the INSTRUCTOR table, the outer query evaluates the inner query. It checks to see if the current row's INSTRUCTOR_ID value exists for the SECTION table's INSTRUCTOR_ID column. Only if a row with the appropriate value is found, the condition is true and the outer row is included in the result.

NOT EXIST Operator:

The NOT EXISTS operator is the opposite of the EXISTS operator; it tests if a matching row cannot be found.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!