Subqueries

A subquery is a SELECT statement within another statement. Here is an example of a subquery:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

The main advantages of subqueries are:

• They allow queries that are structured so that it is possible to isolate each part of a statement.
• They provide alternative ways to perform operations that would otherwise require complex joins and unions.
• Many people find subqueries more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”

Example:
Find the customer name for the order 10422. Instead of using a join we can use subquery as follows.

SELECT customername FROM customers 
WHERE customernumber = (SELECT customernumber FROM orders WHERE ordernumber=10422);

Subqueries with ANY/SOME Keyword

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” The word SOME is an alias for ANY.
Syntax:

SELECT s1 FROM t1 WHERE s1 comparison_operator ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 comparison_operator SOME (SELECT s1 FROM t2);

Where comparison_operator is one of these operators:

= > < >= <= <> !=

Example:

SELECT employeeNumber, firstName FROM employees 
WHERE employeeNumber > ANY (SELECT salesRepEmployeeNumber FROM customers);

Subqueries with IN Keyword

IN returns true if the comparison matches with one of the values that the subquery returns.

When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

‘IN’ and ‘= ANY’ are not synonyms when used with an expression list. ‘IN’ can take an expression list, but ‘= ANY’ cannot.

SELECT s1 FROM t1 WHERE s1 IN (val1, val2, …);

We cannot do the same with ANY.
Example:

SELECT emp_no, salary FROM salaries WHERE salary IN (100000, 120000, 150000);

We can also negate the result using NOT IN as follows.

SELECT emp_no, salary FROM salaries WHERE salary NOT IN (100000, 120000, 150000);

Subqueries with ALL Keyword

The word ALL, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.”

Syntax:

SELECT s1 FROM t1 WHERE s1 comparison_operator ALL (SELECT s1 FROM t2);

Where comparison_operator is one of these operators:

= > < >= <= <> !=

Example:
Find Employees who have not yet represented any customers.

SELECT employeeNumber, firstName FROM employees 
WHERE employeeNumber <> ALL 
(SELECT distinct salesRepEmployeeNumber FROM customers WHERE salesRepEmployeeNumber IS NOT NULL);

NOT IN is an alias for <> ALL. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

Subqueries with EXISTS and NOT EXISTS Keywords

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

Syntax:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);

Example:

SELECT customername FROM customers as cust 
WHERE EXISTS 
(SELECT * FROM payments WHERE cust.customernumber = payments.customernumber) LIMIT 10;