Subqueries

This YouTube video was created by Steve Griffith.

A subquery is a SELECT statement within another statement. In most cases, subqueries can be used in place of a join, and have the advantage of being able to query the same table twice, which is not possible with a join.

Note

Subqueries are often easier to read over joins, but joins get better performance.

# Getting all movies from a list of years
SELECT *
FROM `movies`
WHERE `year` IN (1986, 1997, 1999, 2009);

Subqueries can be used to get data from another table.

# Getting all movies that have the "Fantasy" genre
SELECT *
FROM `movies`
WHERE `genre_id` IN 
  (SELECT `genre_id` FROM `genres` WHERE `genre_title` = 'Fantasy');

Try it yourself on DB Fiddle