Sub-Queries in SQL Statements

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s