Better style in SQL?-CodePudding

I have several queries that look like this:

SELECT name 
FROM people 
WHERE id IN (SELECT person_id 
             FROM directors 
             WHERE movie_id IN (SELECT id 
                                FROM movies 
                                WHERE id IN (SELECT movie_id 
                                             FROM ratings 
                                             WHERE rating = 10)));

It works, but I don't think that nesting so many queries will work in the long term, readability-wise. What is the convention on how to write that in a more legible way? Would you just use one line for every query?

CodePudding user response：

You can do it in this way:

SELECT name 
FROM people  p 
join directors d on d.person_id = p.id
join movies m on m.id = movie_id IN (SELECT id 
join ratings  r on r.movie_id = m.id;

CodePudding user response：

My personal way of formatting SQL statements is to indent using 2 spaces. I've found this improves readability [to me]. For example:

SELECT name 
FROM people 
WHERE id IN (
  SELECT person_id 
  FROM directors 
  WHERE movie_id IN (
    SELECT id 
    FROM movies 
    WHERE id IN (
      SELECT movie_id 
      FROM ratings 
      WHERE rating = 10
    )
  )
);