Subqueries

Examples are based on the Travel Agency database

At Higher level, subqueries were created by saving them as SQL views, and then including the view in the FROM clause of the outer query.

However, modifying the full query would involve editing both the main query and the view separately.

Including subqueries in the WHERE clause of a query keeps everything is in one place.

Subquery in the WHERE clause of a SELECT query

A subquery is a query embedded within the WHERE clause of another SQL query. A subquery is sometimes referred to as an inner query or a nested query, and an SQL query is sometimes referred to as the outer query or the parent query.

The subquery executes before the main query, so the results can be passed to the main query as a condition to further restrict the data to be retrieved.

There are a few rules that subqueries must follow:

  • Subqueries must be enclosed within brackets.
  • Unless the main query has multiple fields in its SELECT clause, a subquery can have only one field in its SELECT clause.
  • The BETWEEN operator can be used within a subquery but cannot be applied to the results of a subquery returned to the main query.
  • Although an ORDER BY clause can be used with the main query, an ORDER BY clause cannot be used in a subquery; if it is needed, the GROUP BY clause can be used to perform the same function as the ORDER BY within a subquery.
SELECT field names
FROM table names
WHERE fieldName OPERATOR
    (SELECT field names
    FROM table names
    WHERE condition)
ORDER BY field names;

Single-Value subqueries

Many subqueries return exactly one record (called single-value subqueries). The developer must check that this is the case, because an error will be generated if a subquery returns more results than expected.

-- Display details of most expensive hotel
SELECT hotelName, starRating, pricePersonNight
FROM Hotel
WHERE pricePersonNight = (SELECT MAX(pricePersonNight)
                          FROM Hotel );

-- Display details all hotels that have a below-average star rating.
SELECT resortName, hotelName, starRating
FROM Resort, Hotel
WHERE Resort.resortID = Hotel.resortID
AND starRating < (SELECT AVG(starRating) 
                  FROM Hotel);
/* Display details of the customer who booked the same hotel 
as the customer with ID 111. */
SELECT firstName, surname, postcode
FROM Customer, Booking
WHERE Customer.customerNo = Booking.customerNo
   AND NOT Customer.customerNo = 111
   AND hotelRef = (SELECT hotelRef 
                   FROM Booking
                   WHERE customerNo = 111
                  );

Multiple-Value subqueries

Subqueries that return more than one row (called multiple-value subqueries), can only be used with multiple-value operators such as EXISTS, IN and ANY.

IN operator

This allows multiple values to be specified as an alternative to multiple OR conditions.

/* display data on all hotels booked by customer ID 315.
The subquery generate list of all hotelRefs that customer 315 has booked.
The outer query looks for hotels that have a hotelref in the subquery. */
SELECT hotelName, starRating
FROM Hotel
WHERE hotelRef IN (SELECT hotelRef FROM Hotel, Booking
                    WHERE Hotel.hotelRef = Booking.hotelRef
                    AND customerNo = 315);

ANY operator

This returns true if any of the subquery values meet the condition specified in the main query.

Note: SQLite does not support the ANY operator. However you can still be required to complete design and coding questions that use this.

  • WHERE fieldname > ANY (subquery)
    is equivalent to greater than the minimum value in the subquery’s list
  • WHERE fieldname < ANY (subquery)
     is equivalent to less than the maximum value in the subquery’s list
  • WHERE fieldname = ANY (subquery)
     is equivalent to IN subquery’s list
/*Display the customer number, hotel reference, and booking cost for any booking 
that costs more than any bookings made by customers with surnames Lowden, Shawfair or 
Sheriffhall.
The subquery returns list of costs for all booking made by the three specified customers
The outer query finds all bookings that cost more than any of the subquery cost */
SELECT customerNo, 
       Hotel.hotelRef, 
       pricePersonNight*numberNights*numberInParty AS [Booking Cost]
FROM Booking, Hotel
WHERE Booking.hotelRef = Hotel.hotelRef
      AND pricePersonNight*numberNights*numberInParty > ANY
          (SELECT pricePersonNight*numberNights*numberInParty
           FROM Booking, Hotel, Customer
           WHERE Booking.hotelRef = Hotel.hotelRef
           AND Booking.customerNo = Customer.customerNo
           AND surname IN ("Danderhall", "Lowden", "Shawfair")
          );

EXISTS

This tests for the existence of records within the subquery and returns true when the subquery returns one or more records (this is very useful to obtain records that do not meet a certain condition).

Notes:

  • EXISTS only checks for presence of any records. The SELECT clause of the subquery could return any field, * is returned to keep the SQL simpler/readable.
  • The subquery’s WHERE clause links to a table that is not in its own FROM clause, but to a table in outer query
/* Display details of all 3-star hotels that have bookings. */ 
SELECT hotelname, mealPlan, starRating, resortName
FROM Hotel, Resort
WHERE Hotel.resortID = Resort.resortID
      AND starRating = 3
      AND EXISTS (SELECT * FROM Booking
                  WHERE Booking.hotelRef = Hotel.hotelRef)

-- Display details of customers who have never made a booking.
SELECT firstName, surname, address
FROM Customer
WHERE NOT EXISTS (SELECT * FROM Booking
                  WHERE Customer.customerNo = Booking.customerNo);

Multiple Subqueries

Two independent subqueries:

/* Display the name, star rating, and total of nights booked for hotels that have:
- a total number of customer nights booked that is more than the total number of nights
  booked by the customer with ID 290 (number of nights booked multiplied by number in party)
- and a star rating which is less than that of the hotel with the highest star rating
The query should list the hotels from lowest star rating to the highest.*/
SELECT hotelName, starRating, 
       SUM(numberNights*numberInParty) AS [Customers Nights]
FROM Hotel, Booking
WHERE Hotel.hotelRef= Booking.HotelRef
      AND numberNights*numberInParty > ( SELECT SUM(numberNights*numberInParty) 
                                         FROM Booking
                                         WHERE customerNo =290)
      AND starRating < (SELECT MAX(starRating) FROM Hotel)
GROUP BY hotelName, starRating
ORDER BY starRating;

A subquery inside a subquery:

/* Display details of the second most expensive hotels */
-- innermost subquery finds most of expensive price of all hotels
-- subquery then finds largest price that is less than the most expensive price
SELECT * 
FROM hotel
WHERE pricePersonNight = ( SELECT MAX(pricePersonNight)
                           FROM hotel
                           WHERE pricePersonNight < ( SELECT MAX(pricePersonNight)
                                                      FROM hotel )
                         )