Rashidul Rashidul - 6 months ago 19
SQL Question

Query Performance INNER JOIN ON AND comparison

I would like to know which one is best regarding performance between the 2 queries stated below or do they perform identically?

First one: [without WHERE clause, just AND with ON]

SELECT related_tabid AS tabid, label, t.name
FROM relatedlists r
INNER JOIN tab t
ON t.tabid = r.tabid
AND t.name = 'Leads'
AND r.is_active=1 and r.related_tabid <> 0
AND t.is_active=1
ORDER BY label


Second one: [using WHERE clause, AND associated with where instead of ON ]

SELECT related_tabid AS tabid, label, t.name
FROM relatedlists r
INNER JOIN tab t
ON t.tabid = r.tabid
WHERE t.name = 'Leads'
AND r.is_active=1 and r.related_tabid <> 0
AND t.is_active=1
ORDER BY label

Answer

Both queries are the same because the join used is INNER JOIN. INNER JOIN basically it filters only rows that has at least a match on the other table. Even the two tables are interchange, the result is still the same.

But if you are joining them via LEFT JOIN, the two queries are different from each other and will yield different result.

Comments