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;
Advertisement
Answer
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).