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%' ) );
- 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 );
- Sub query and outer query both are independent
- Sub query has dependency on outer query