Multiple nested Vs Correlated sub-query tricky top differences

Multiple nested query and Correlated sub-query both are different. Try to understand these with examples

Multiple nested query-A subquery can contain another query, making it a query with multiple nested subqueries.


SELECT customer.*
FROM customer
WHERE c_no IN
(SELECT c_no
FROM c_order
WHERE p_code IN
(SELECT p_code FROM product WHERE p_name NOT LIKE '%Nut%'
)
);

My analysis

  • All the nested SQL queries run in sequence to get the result

Co-related sub-query -All the preceding subqueries are independent of their outer queries. 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.


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
);

My analysis

  • Sub query and outer query both are independent
  • Sub query has dependency on outer query

Also read

Advertisements

Author: Srini

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