Sub-queries are often used in SQL statements. A sub-query can be used in the WHERE clause as well as in the FROM clause. Let’s visit a few examples for each of these scenarios:
Sub-Query in WHERE clause:
Problem: List all teachers whose department name starts with a C
SELECT * FROM teacher WHERE department_id IN (SELECT id FROM department WHERE name like 'C%');
Sub-Query in a FROM clause: This is a little more complicated example. I’ll describe the table schema in order to understand this better.
acc_num | curr_bal | branch_num
Problem: List all accounts along with their current balance and branch number; whose balance is greater than the average balance of the branch to which they belong. i.e
SELECT acc_num, curr_bal, branch_num FROM account WHERE curr_bal > (average balance of branch)
Let’s first start with a query that can get us the average balance of the branch.
SELECT branch_num, AVG(curr_bal) AS avg_bal FROM account GROUP BY branch_num
But, the sub-query cannot be added in the above place holder because the average balance has to be for the specific account branch. The average balance query returns multiple rows. Therefore, it makes sense to use the result of this query as a table itself and join this with the account table and try to get some results.
SELECT a.acc_num, a.curr_bal, a.branch_num FROM account a, (SELECT branch_num, AVG(curr_bal) AS avg_bal FROM account GROUP BY branch_num) b WHERE a.curr_bal > b.avg_bal AND a.branch_num = b.branch_num