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 (Room.price), we can do it as follows:
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:
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.
This same solution could be executed without comparison by multiple columns, but it would be more verbose:
SELECT Reservations.* FROM Reservations INNER JOIN Rooms ON Reservations.room_id = Rooms.id WHERE Reservations.price = Rooms.price;