SQL Subqueries with Examples

Introduction

A complete SELECT statement can be embedded (subselect) within another

SELECT statement. A subselect can be used in the WHERE and HAVING clauses of the outer SELECT statement (nested query). A subquery can be used immediately following a relational operator. Subquery always enclosed in parentheses.

Type of subquery

A scalar subquery returns a single column and a single row (singlevalue).
A row subquery returns multiple columns, but a single row.
A table subquery returns one or more columns and multiple

The following rules apply to subqueries:

The ORDER BY clause may not be used in a subquery .
The subquery SELECT list must consist of a single column name or expression, except for subqueries that use the keyword EXISTS.
By default, column names in a subquery refer to the table name in theFROM clause of the subquery. It is possible to refer to a table in a FROMclause in an outer query by qualifying the column name; in this case thesubquery is called a correlated subquery.
When a subquery is one of the two operands involved in a comparison, thesubquery must appear on the right-hand side of the comparison.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME from TABLE_NAME WHERE ... );

Examples

STAFF (sno, fname, lname, position, sex, DOB, salary, bno)

BRANCH (bno, street, city, postcode)

List the staff who work in the branch at ‘163 Main St’.

SELECT sno, fname, lname, position
FROM staff
WHERE bno = (SELECT bno
FROM branch
WHERE street = '163 Main St');

Explanation: First bno from staff table is selected where street equals 163 main st. Then using bno sno,fname,lname,position is retrieved.

STAFF (sno, fname, lname, position, sex, DOB, salary, bno)

List the staff whose salary is greater than the average salary, and list by howmuch their salary is greater than the average.

SELECT sno, fname, lname, position, salary - (SELECT avg(salary) FROM staff ) AS sal_diff FROM staff WHERE salary > ( SELECT avg(salary) FROM staff );

Explanation: Average salary is retrieved from staff table. Then sno,fname,lname,position and salary(salary minus the average salary) is retrieved where salary is greater than average salary.

Example using IN

PROPERTYFORRENT (pno, street, area, city, pcode, type, rooms, rent, sno)

STAFF (sno, fname, lname, position, sex, DOB, salary, bno)

BRANCH (bno, street, city, postcode)

List the properties that are handled by staff who work in the branch at ‘163 MainSt’.

SELECT pno, street, area, city, pcode, type, rooms, rent
FROM property_for_rent
WHERE sno IN
(SELECT sno
FROM staff
WHERE bno =
(SELECT bno
FROM branch
WHERE street = '163 MainSt'));

Explanantion: Here different tables are used to retrieve the data. First bno is selected from branch table followed by sno from staff followed by other details from propertyforrent table.

Subqueries using Any/All

Used with subqueries that produce a single column of numbers.
If the subquery is preceded by the keyword ALL, the condition will only betrue if it is satisfied by all values produced by the subquery.
If the subquery is preceded by the keyword ANY or SOME, the condition will be true if it is satisfied by any (one or more) values produced by thesubquery.

Example:

STAFF (sno, fname, lname, position, sex, DOB, salary, bno)

Find staff whose salary is larger than the salary of at least one member of

staff at branch B3.

SELECT sno, fname, lname, position, salary
FROM staff
WHERE salary > SOME
(SELECT salary
FROM staff
WHERE bno = 'B3');

Explanantion: some returns true if it returns atleast one row. Salary is retrieved from the staff table where bno is B3. If salary is greater than retrieved salary then row is returned with specified attributes.

Conclusion

Subquery is used to get values from two or more tables. Subqueries make the process of finding answers to query simple. Various methods and examples were discussed in the article.