CODEWITHSUNDEEP
sqloracle-databasejoin
curious
curious

Reputation: 11

Multiple joins in a sql query - which is best option

I want to use a sql query with multiple joins similar to the example below.

SELECT t1.column1, t1.column2, t1.column3
FROM 
table1 t1 
LEFT JOIN table2 t2 ON (t1.id1 = t2.id)
LEFT JOIN table3 t3 ON (t1.id1 = t3.id)
JOIN table4 t4 ON t1.id2 = t4.id 
WHERE
...

Would this give different results than the following query:

SELECT t1.column1, t1.column2, t1.column3
FROM 
table1 t1 
LEFT JOIN table2 t2 ON (t1.id1 = t2.id)
LEFT JOIN table3 t3 ON (t2.id = t3.id)
JOIN table4 t4 ON t1.id2 = t4.id 
WHERE
...

If they are both 'correct' is the second more efficient than the first?

Thanks

Upvotes: 0

Views: 57

Answers (2)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

The second one is more efficient because it will always return the same or less amount of data.

In the first query you are asking for all the records that are both in table1 and table4 + records from table2 if they exists in table1 + records from the table3 if they exists in table1.

In the second query you are asking for all the records that are both in table1 and table4 + records from table2 if they exists in table1 + records from the table3 if they exists BOTH in in table1 and in table2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The queries are different, so this isn't a performance issue. The difference are these lines:

LEFT JOIN table3 t3 ON (t1.id1 = t3.id)

and

LEFT JOIN table3 t3 ON (t2.id1 = t3.id)

For the first, t3.id needs to only match t1.id. For the second, it needs to match t2.id1, which in turn must also match t1.id. In other words, the second version requires that the id be in both t1 and t2.

This is because of the LEFT JOIN. The queries would be equivalent if they used INNER JOIN.

Upvotes: 3

Related Questions