Sub Query vs Correlated Sub Query

Why we need sub queries? The reason is to avoid writing multi-Queries.

There are two types of queries. Sub query and Correlated Sub Query.

Sub-Query Example..

EMP Table:
empname  empno     sal
abcd      101        2000
abbb      102        10000

SQL Query on Sub-Query

select ename from emp a
where sal=(select max(sal) from emp b);
The result: abbb

The aproach is IN-OUT. That means first it executes internal query and then outer query executes.

Sub-Query Vs Join

If you only want to see a report where the final result set has only columns from one table, use a Subquery. Obviously, if you need columns on the report where the final result set has columns from both tables, you have to do a Join.

All Sub-queries are independent of Outer Query.

Definition for Correlated Sub-query

A subquery can also be related to its outer query, where one or more column from the outer query table is (are) related to the column(s) of the subquery table in the WHERE clause of the subquery. This type of subquery is called the correlated subquery.

Example Correlated Subquery

SELECT customer.*
FROM customer
  (SELECT c_no
  FROM c_order o
  JOIN product p
  ON o.p_code = p.p_code
  WHERE p_name NOT LIKE '%Nut%'
  AND customer.c_no = o.c_no

Result of Correlated sub query

---- --------------
40   Super Agent
10   Standard Store
20   Quality Store

What is the flow in Correlated subquery..

A correlated subquery that returns only customers who have not ordered any product whose name contains ‘Nut’. Note that the c_no column of the outer query table, customer, is related to the c_no column of the c_order table of the subquery.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.