Subquery in SQL: Subquery is a query inside a query. The result of a subquery is used for the calculations of the main query. Subqueries usually use WHERE clause or HAVING clause to connect with the main query. ORDER BY clause is not used in a subquery. A subquery can refer to the columns in tables of the main query. In the below given example the subquery references a column COMPANY which belongs to the table COMPANIES. This reference is known as outer reference.
SELECT CITY FROM COMPANIES WHERE TOTAL_SALES > (SELECT SUM(AVG_SALES) FROM EMPLOYESS WHERE EMPL_COMPANY = COMPANY)
Search conditions in subquery:
Subquery comparison: For comparing value of one expression to value of produced by subquery.
SELECT NAME FROM EMPLOYEES WHERE AVG_SALES > (SELECT TOTAL_SALES FROM COMPANIES WHERE CITY = ‘Chicago’)
Here, the following comparison can be done: =, <>, <, <=, >, >=
where, <> is the inequality comparison.
Subquery set membership: It is used to check if value of expression is matching a set of values produced by a subquery. For subquery set membership condition, IN keyword is used.
SELECT NAME FROM EMPLOYEES WHERE EMPLOYEE IN (SELECT COMPANY FROM COMPANIES WHERE AVG_SALES > TOTAL_SALES)
Subquery existence: It is used to check if a subquery produces any rows.
EXISTS keyword: For subquery existence condition, EXISTS keyword is used.
SELECT DISTINCT DETAILS FROM ITEMS WHERE EXISTS (SELECT ORDER_N FROM ORDERS WHERE ITEM = ITEM_ID AND AMOUNT >= 10000.00)
If there are results from the subquery then TRUE is returned, otherwise FALSE.
NOT EXISTS keyword: Logic of EXISTS can be reversed by using NOT EXISTS keyword. If there are no results from the subquery then TRUE is returned, otherwise FALSE.
Subquery quantified: It is used to compare value of an expression to every value produced by a subquery. ANY and ALL keywords are used for this purpose.