Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
15. The IN operator with correlated subqueries
Other 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.ID = City.CountryID
    AND 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.ID = Trip.CityID
    AND City.Rating < 4
);