| SQL inline View subquery | SQL Correlated subquery | SQL Scalar subquery |
Subquery is a query inside a main query.
SQL subquery can be embedded:
- As a column expression in the main SQL statement
- As a filter inside the WHERE (or HAVING) clause in the main SQL statement
- As a datasource inside the FROM clause in the main SQL statement
- Enclose the subquery in parentheses
- Do not use a semicolon at the end of the subquery statement
Find the names of all of the managers in the Employees table.
Employees (emp_id, Emp_name, ManagerID)
SELECT Emp_name AS Employee
FROM Employees
WHERE emp_id IN (SELECT DISTINCT ManagerID FROM Employees)
Example 2 of SQL Subquery
Store_Info (store_name, sales, date)
Location (region_names, store_name)
Find the sales of all stores in the East region.
SELECT SUM(Sales) FROM Store_Info
WHERE Store_name IN
(SELECT store_name FROM Location
WHERE region_name = 'East')
You can nest any number of such queries; Oracle does not have a limit.
There is also another term used NESTED SUBQUERIES. when you use subqueries in the WHERE clause of the SELECT statement it is called nested subquery. There are only 255 levels of subqueries.