In SQL we can use a subquery in several parts of the query. We can use a subquery in the WHERE clause (which is the natural place for the subquery), in the FROM clause, in the SELECT list, or even in the HAVING clause. Moreover, we can use subqueries in the UPDATE, DELETE and INSERT statements. However, in this article, we will focus on subqueries used in the WHERE clause and especially on the operator used to compare the results of the subquery.
THE EASY EXAMPLE
The first example we will use is a subquery to obtain the highest salary in the employee table, then we will obtain the names of the employee which earn this salary:
Sounds easy, however, this subquery has a restriction, it must be a single record subquery. In other words, this subquery cannot return 0 records or more than 1 records. It just can return 1 record. The developer must be sure about that, an error will occur if subquery doesn’t return exactly one record.
Operators allowed to use with single record subqueries are:
|greater or equal than||>=|
|less or equal than||<=|
One interesting query we can create using one of this operator is: Suppose we did a general raise in april 2018, and we would like to know if we still have employees with a salary lower than the company average salary in march 2018. We can use the following query:
MULTIPLE RECORD SUBQUERIES
If we have a subquery returning 0, 1 or more records, we have a multiple record subquery, and we need to use another set of operators to compare with it. For example, if we want to obtain the employees who received a bonus in March we can do a subquery like the following:
As we can see, the subquery returns 2 records, so it is a multiple record subquery. Then we can use it in the following query:
As we can see in the previous image the operator used is the IN, which works similar to the “belong to” operator of set operations. Other operators available to compare with multiple record subquery are shown in the following table:
|greater than all||> ALL|
|greater than any||> ANY|
|less than all||< ALL|
|less than any||< ANY|
|not equal to all||<> ALL|
|equal any||= ANY|
An interesting query here can be obtained the maximum value in some aggregation function. For example, if we have the average salary per department, we can try to obtain the department with the least average in the company. These operators open a door to solve this type of queries without using limit and order by clause. Let’s see the query.
THE EXISTS OPERATOR
The last operator we will cover is EXISTS (or NOT EXISTS) which is a particular operator. EXISTS returns TRUE if the subquery returns at least one records, no matter what values have the records, or how many records are in the result. It is very useful when we want to obtain records which don’t have any kind of property. For example, if we want the names of the employee who never receive a bonus, we can execute the following query.
In the previous query, we selected the constant ‘1’ instead of a regular column just as a way to show to the reader we don’t need to select any real column from the table. Any constant is enough. The important thing is: Does the subquery return records or not? We also need to mention that subqueries used with EXISTS or NOT EXISTS are usually correlated subqueries. This kind of subquery are executed one time per candidate record (of the outer query) to be in the result set. Then, this type of queries can have performance issues.
We covered the different type of operators for subqueries. We first explained single record subquery operators, then we covered multiple record subqueries and finally we explained EXISTS operator with correlated subqueries.