Using Subqueries

• A subquery is a query that is used in SQL statements; most commonly, this is another query.
• The query containing the subquery is called the outer query.
• In this context, the subquery is also referred to as the inner query.

A subquery can be used to achieve the following:

• Make a SELECT statement more readable.

• Improve the performance of a SELECT statement, however, with a perfect optimizer, this should not be necessary.

• Formulate a SELECT statement that cannot be formulated (or only with difficulty) without a subquery. For example, it is not possible to put aggregate expressions in the WHERE clause of a SELECT statement, unless they are part of a subquery.

There are different types of subqueries:

• Expression subqueries return exactly one row; quantified predicate subqueries may return zero or multiple rows.

• Uncorrelated subqueries are complete SELECT statements that may be run on their own. Correlated subqueries are incomplete and must refer to the outer query.

These two determinations (expression versus quantified predicate; uncorrelated versus correlated) are independent. This gives four possible types of subqueries.

Expression Subquery

An expression subquery returns exactly one row (CarID values are unique). Boolean expressions may be used to compare against the subquery.

Code Snippet

Copy codeSwitch to dark mode

12345
SELECT *
    FROM Owner
WHERE OwnerID = (SELECT	Owner
		 FROM Car
		 WHERE CarID = ꞌF08ꞌ);

Quantified Predicate Subquery

A quantified predicate subquery may return zero or many rows (there may be any number of owners in the city of Wiesloch).

Boolean expressions may not be used because one value cannot be equal to (or greater, or less than) multiple values. IN and EXISTS may be used instead.

Code Snippet

Copy codeSwitch to dark mode

12345
SELECT *
    FROM Car
WHERE Owner IN  (SELECT	OwnerID
		 FROM Owner
		 WHERE City = ꞌWieslochꞌ);

Uncorrelated Subquery

An uncorrelated sub​​query makes no reference to the outer query.

Code Snippet

Copy codeSwitch to dark mode

12345
SELECT *
    FROM Car
WHERE Owner IN (SELECT OwnerID
                FROM Owner
                WHERE City = 'Wiesloch');

Correlated Subquery

A correlated subquery refers to the outer query.

Code Snippet

Copy codeSwitch to dark mode

123456
SELECT *
    FROM Car c
WHERE EXISTS (SELECT *
              FROM Owner o
              WHERE o.OwnerID = c.Owner
                AND o.City = 'Wiesloch'); 

This query is uncorrelated because the inner query is complete.

• The inner query, if run on its own, would return the OwnerID values H01 and H06.

• The outer query is equivalent to one using the logical test.

  WHERE Owner IN (ꞌH01ꞌ , ꞌH06ꞌ)

Which cars are registered to an owner from Wiesloch?

12345
SELECT *
    FROM Car
WHERE Owner IN (SELECT OwnerID
                FROM Owner
		WHERE City = ꞌWieslochꞌ);

You can use = ANY if the outer value should match any result value of the subquery.

Which cars are registered to an owner from Wiesloch?

12345
SELECT *
    FROM Car
WHERE Owner = ANY (SELECT OwnerID
		   FROM Owner
		   WHERE City = ꞌWieslochꞌ);

= ANY is equivalent to IN, but you can use the ANY operator with other comparison operators:

You can use > ANY if the external value should be greater than any value of the subquery.

Which officials have more than the minimum overtime hours?

1234
SELECT Name, Overtime
    FROM Official
WHERE Overtime > ANY (SELECT Overtime
		      FROM Official);

You can use > instead of > ANY if the subquery results in only a single value.

Which officials have more than the minimum overtime hours?

1234
SELECT Name, Overtime
    FROM Official
WHERE Overtime > (SELECT MIN(Overtime)
		  FROM Official);

You can use < ALL if the outer value should be less than all result values of the subquery (other comparison operators are possible).

The ANY and ALL operators allow for comparisons against quantified predicate subqueries.

Which officials have the fewest overtime hours?

12345
SELECT Name, Overtime
    FROM Official
WHERE Overtime <= ALL (SELECT Overtime
		       FROM Official
		       WHERE Overtime IS NOT NULL);

However, the ANY and ALL operators are frequently misunderstood. If the quantified predicate subquery can be made into an expression subquery, ANY and ALL can be avoided.

The MAX and MIN aggregates can be used to convert the subquery.

• Which officials have the fewest overtime hours?
• For the example below = would be possible instead of <= as well.

1234
SELECT Name, Overtime
    FROM Official
WHERE Overtime <= (SELECT MIN (Overtime)
		   FROM Official);

A correlated subquery must refer to the outer query in order to calculate its results; it is not possibly to syntactically execute on its own. As a consequence, the correlated subquery may have to execute repeatedly, as different reference values from the outer query are passed to the subquery. In the worst case, this may require a separate execution for every row in the outer query. Therefore, it is important to write correlated subqueries to be as efficient as possible, and to bypass as many executions as possible..

By using EXISTS you can check that the query result of the subquery is not empty; specific values are not important, just that at least one row would be returned.

Which vehicles are registered to an owner from Wiesloch?

12345
SELECT *
    FROM Car c
WHERE EXISTS (SELECT *
              FROM Owner o
              WHERE o.City = 'Wiesloch' AND o.OwnerID = c.Owner);

Even with a correlated subquery, you can omit table aliases if the column names are unique.

What vehicles are registered to an owner from Wiesloch?

12345
SELECT *
    FROM Car 
WHERE EXISTS (SELECT *
              FROM Owner 
              WHERE City = 'Wiesloch' AND OwnerID = Owner);

By using NOT EXISTS you can check if the result of the sub query is empty.

Which cars are not reported as stolen?

12345
SELECT CarID
    FROM Car c 
WHERE NOT EXISTS (SELECT *
		  FROM Stolen s 
		  WHERE s.PlateNumber =	c.PlateNumber);

You can use the same table in both the FROM clause of the outer query and the FROM clause of the correlated subquery.

Which vehicle has the most HP?

12345
SELECT *
    FROM Car c1
WHERE NOT EXISTS (SELECT *
                  FROM Car c2
                  WHERE c2.HP > c1.HP);

Even when using a correlated subquery, you can ANY or ALL comparisons. For example:

How much horsepower has the most powerful car of each brand?

12345
SELECT DISTINCT Brand, HP
    FROM Car c1
WHERE c1.HP >= ALL(SELECT c2.HP
                   FROM Car c2
                   WHERE c2.Brand = c1.Brand);

The WHERE clause of a correlated subquery can contain an additional correlated subquery.

Who are the registered owners of at least one stolen car?

123456789
SELECT o.Name
    FROM Owner o 
WHERE EXISTS (SELECT *
	      FROM Car c 
	      WHERE c.Owner = o.OwnerID
              AND EXISTS (SELECT *
                          FROM Stolen s 
 	                  WHERE s.PlateNumber = c.PlateNumber)
	      );

You can also use a correlated subqquery in the SELECT clause.

How much horsepower is each car lacking compared to the most powerful car of the same brand?

1234
SELECT CarID, Brand, (SELECT MAX (HP)
                      FROM Car c2
                      WHERE c2.Brand = c1.Brand) – HP AS  Difference
    FROM Car c1;

Multi-level nesting is not limited to correlated subqueries; any subquery may be nested to any depth, although very deep nestings can be difficult to understand. As noted previously, any valid operation in a SELECT may be combined with any other valid operation(s), so all previous operations will also work with subqueries and multi-level subqueries.

You can use functions in a subquery.

Which owners were born in the same year as the youngest owner?

1234
SELECT *
FROM Owner 
WHERE YEAR(Birthday) = (SELECT MAX(YEAR(Birthday))
 	                FROM Owner);

You can compare against multiple columns in the subquery.

To whom within the EU is (at least) one blue car registered?

12345
SELECT Name
    FROM Owner_EU
WHERE (Country, OwnerID) IN (SELECT Country, Owner
                      	     FROM Car_EU
                     	     WHERE Color = ‘blue');

The WHERE clause of a sub query can contain another subquery.

Who are the owners of stolen cars?

1234567
SELECT Name
    FROM Owner
WHERE OwnerID IN (SELECT Owner
		  FROM Car  
		  WHERE PlateNumber IN (SELECT PlateNumber
                                        FROM Stolen)
		 );

You can combine joins and subqueries.

To whom is the most powerful car of each brand registered?

123456
SELECT DISTINCT Brand, Name 
    FROM Owner RIGHT OUTER JOIN Car ON OwnerID = Owner
WHERE (Brand, HP) IN (SELECT Brand, MAX (HP)
  	              FROM Car
		      GROUP BY Brand)
ORDER BY Brand ASC, Name ASC;

A subquery is usually part of the FROM clause, but may be used in other clauses.

Which blue cars have less than 120 HP?

12345
SELECT *
    FROM (SELECT PlateNumber, HP AS Power
	  FROM Car  
	  WHERE Color = ‘blue')
WHERE Power < 120;

What is the HP deviation of each car when compared to the most powerful yellow car?

12345
SELECT CarID, Brand, (SELECT MAX (HP)
		      FROM Car  
		      WHERE Color = ‘yellow') – HP AS Deviation
    FROM Car
ORDER BY 3 DESC;