Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Instruction
Great job! Now, remember the IN operator? It allows us to specify a few values in the WHERE clause as opposed to just one value, so it worked a bit like the OR operator coupled with equality. Now, take a look:
SELECT
*
FROM City
WHERE City.CountryId IN (
SELECT
Country.ID
FROM Country
WHERE Country.Population < 40000
);
Can you predict what the above instruction does? It shows all cities from those countries that have a total population of less than 40000.
Exercise
Show all information about all trips to cities with a city rating lower than 4.
Stuck? Here's a hint!
Type:
SELECT
*
FROM Trip
WHERE Trip.CityID IN (
SELECT
City.ID
FROM City
WHERE City.Rating < 4
);
