Using Two LEFT JOIN or use AND with single LEFT JOIN?

Question

Using two LEFT JOINs i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
LEFT JOIN Table2 AS t3 ON <condition2>

is this same as using AND in single LEFT JOIN? i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
AND <condition2>

Both are same or different(in general)?

• user98113: It depends on your requirement and ON condition. If you put t1.col1=t2.col1 with Table2 and t1.col1.t3.col1 It would be same.
• Andriy M: The second query would be closer to the first one in terms of results if it used OR instead of AND, but it would still be a different query in general.
• Arnab Datta: Try using Explain plan to see the difference :)
• ypercubeᵀᴹ: @RajeshRanjan no they wouldn't be equivalent, even in that case.

Answer 1

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria.

Suppose you have Table2 with the following content:

| Color | Size |
|-------|------|
| Red | S |
| Blue | S |
| Blue | XS |

Suppose you want to have the rows that are Blue and S. In your first option you get all rows (t2 for example with all Blue and t3 with all S) and in your second option you only get row 2.

• Andriy M: Not only that, but since this is about outer-joining the table twice, you will also get a mini-Cartesian product with the first query. I mean, a Table1 row wants colour blue and size S. So, the first join gives it two Table2 matches, thus duplicating the Table1 row, and the second join gives two matches for each of the copies, thus resulting in four rows in total.