Multi-column subqueries

So far, we have only considered subqueries that return a single column. However, we can also work with subqueries that return multiple columns and multiple rows (derived tables).

Comparison with multiple columns

SQL supports comparison not only with a single column, but also allows pairwise comparison of values in the main query with values in the subquery.

For example, if we want to get information about all reservations where the accommodation price at the time of booking (Reservations.price) matches the current price of the accommodation (Rooms.price), we can do it as follows:

MySQL
SELECT * FROM Reservations
    WHERE (room_id, price) IN (SELECT id, price FROM Rooms);

In this example, the subquery returns a table with the identifiers of residential properties and their current price:

MySQL
SELECT id, price FROM Rooms

And then, using this table, we limit all bookings only to those where the pair of values room_id and price can be found in the subquery table.

The same solution can be applied without the need for comparison across multiple columns, although it would be less concise:

MySQL
SELECT Reservations.* FROM Reservations
INNER JOIN Rooms
ON Reservations.room_id = Rooms.id
WHERE Reservations.price = Rooms.price;