Oracle Certification - SQL Fundamentals I - Subqueries
Subqueries
Subqueries can be nested inside a SELECT, INSERT, UPDATE, DELETE or within another subquery. Subqueries may occur in the SELECT, FROM, WHERE or HAVING clauses. Subqueries can be nested a maximum or 255 times the WHERE clause.
Subqueries must have a SELECT & FROM clause, and can additionally have a WHERE, GROUP BY and/or HAVING clause(s).
Types of Subquery
Single-row Subquery
A single-row subquery, or scalar subquery, as the name would suggest, returns a single row. Single-row subqueries are used in conjunction with any of the following operators:
| Operator | Description |
| = | equal |
| > | greater than |
| >= | greater than or equal |
| < | less than |
| <= | less than or equal |
| <> | not equal |
| != | not equal |
So for example:
Multiple-row Subquery
Multiple-row subqueries return more than one row, and are used with the following operators:
| Operator | Description |
| IN | equal to any value in the list |
| NOT IN | not equal to any value in the list |
| ANY | returns rows that match any value in the list |
| ALL | returns rows that match all values on the list |
For example:
Correlated Subquery
A correlated subquery references columns from the parent query.
For Example, selecting the employee with the highest salary(ies) in each department. The subquery does not require a GROUP BY clause as it is restricted to a single department each time it executes:
Note: Correlated subqueries must be re-evaluated for every row in the parent query, and can therefore be very inefficient
Inline Views
When a subquery is used within the FROM clause it creates what is know as an inline view. Essentially the SELECT statement will select from the subquery which is a subset of a table, or a combination of two or more tables.
Conclusion
Next, Set Operators.