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.
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 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 );
Result of Correlated sub query
C_NO C_NAME ---- -------------- 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.