How the SQL query with two left join works?-CodePudding

I need help in understanding how left join is working in below query.

There are total three tables and two left joins.

So my question is, the second left join is between customer and books table or between result of first join and books table?

SELECT c.id, c.first_name, c.last_name, s.date AS sale,
 b.name AS book, b.genre
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
LEFT JOIN books b
ON s.book_id = b.id;

CodePudding user response：

Good question.

When it comes to outer-joined tables, it depends on the predicates in the ON clause. The engine is free to reorder the fetch and scans on indexes or tables as long as the predicates are respected.

In this particular case there are three tables:

customers (c)
sales (s)
books (b)

customers is inner joined so it becomes the driving table; there are other considerations, but for simplicity you can consider that this is the table that is read first. Now, which one is second? sales or books?

The first join predicate c.id = s.customer_id doesn't establish any relationship between the secondary tables; therefore it doesn't affect which table is joined first.

The second join predicate s.book_id = b.id makes books dependent on sales. Therefore, it decides sales is the second table, and books is the last one.

A final note: if you understand the concept of dependency there are several dirty tricks you can use to force the engine to walk the tables in the order you want. I would not recommend to do this to a novice, but if at some point you realise the engine is not doing what you want, you can tweak the queries.

CodePudding user response：

The second join statement specifies to join on s.book_id = b.id where s is sales and b is books. However, a record in the books table will not be returned unless it has a corresponding record in the sales AND customers tables, which is what a left join does by definition https://www.w3schools.com/sql/sql_join_left.asp. put another way, this query will return all books that have been purchased by at least one customer (and books that have been purchased multiple times will appear in the results multiple times).