Subquery Operators

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:

 

subquery operators 01

 

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:

 

Operator name symbol
equal =
greater than >
greater or equal than >=
less than <
less or equal than <=
not equal <>

 

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:

 

subquery operators 02

 

 

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:

 

subquery operators 03

 

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:

 

subquery operators 04

 

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:

 

Operator name symbol
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.

 

subquery operators 05

 

 

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.

 

subquery operators 06

 

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.

 

CLOSING

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.