Subquery Vs. Correlated Subquery – Top Differences

Why do we need subqueries? The reason is to avoid writing multi-Queries. Here’re the differences between Subquery and Correlated Sub Query.

EMP Table

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

Sample 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

All Sub-queries are independent of Outer Query.

If you only want to see a report where the final result set has only columns from one table, you need Subquery. If you need columns on the report where the final result set has columns from both tables, you need Joins.

What is 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
WHERE c_no IN
  (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
  );

Here’s Result of Correlated sub query

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

How Correlated subquery Works

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.

Related Posts