Subqueries

Let's trace how the two SELECT statements work together in the song example:

SELECT
  id,
  song_name,
  artist_id
FROM
  songs
WHERE
  artist_id IN (
    SELECT
      id
    FROM
      artists
    WHERE
      artist_name LIKE 'Rick%'
  );

• The inner query finds the IDs of artists whose names start with Rick.
• The outer query finds songs whose artist_id matches any of those IDs.

So the inner query supplies the values for IN to check. Even if it finds just one artist, there could be many songs that belong to them.

The row-count question refers to the result of the whole query.