I use subqueries once in a while. That's why I am not very strong with them. They are going to be on my OCA SQL Expert exam. Time to bone up on my subquery knowledge. There are different classifications (a subquery may fall in more than 1 category):
- Single row
- Multiple row
- Multiple column
- Correlated
- Scalar
Most subqueries are categorized by how many rows and columns the subquery returns. The exception is the correlated subquery. It references (correlates with) the outer query. A correlated subquery cannot stand on its own. Here is where you can use a correlated subquery:
By the way, an inline view is when a subquery takes the place of a table in the FROM clause of a SELECT query. Now let's get back to the categories. A multiple row subquery needs special comparisons in the outer query:
- IN
- NOT IN
- ANY (also known by SOME)
- ALL
Multiple column subqueries take the form in the WHERE clause as follows - WHERE (col1, col2) = (SELECT col1, col2 FROM ... etc). This one does not seem as intuitive. Then there is the scalar subquery that returns a single row and column. There are a number of places you cannot use a scalar subquery:
- CHECK constraint
- GROUP BY
- HAVING
- Function based index
- DEFAULT value
- RETURNING
- WHEN (from the CASE)
- START WITH / CONNECT BY
EXISTS checks whether there are any rows in the subquery. If so, it returns TRUE. If not, it returns FALSE. You can also use the opposite which is NOT EXISTS. If you use a SELECT that employs the EXISTS, it is called a semijoin.
WITH allows you to name a subquery block. The named queries act like inline views or temporary tables. The name is valid in all of the statement except in the subquery itself.
