If we consider that you use INNER JOIN
instead of LEFT JOIN
(which appears to be your intent), these two queries are functionally equivalent. Query optimizers will review and evaluate criteria in your WHERE
clause and your FROM
clause and consider all of these factors when building query plans in order to reach the most efficient execution plan. If we do an EXPLAIN
on both statements, we get the same result:
Query 1:
EXPLAIN
SELECT
tableA.ColA
,tableA.ColB
,tableA.ColC
,tableA.ColD
,tableA.ColE
FROM tableA
JOIN tableB ON tableA.ColA=tableB.ColA
WHERE
tableA.ColB=tableB.ColB
AND tableA.ColC=tableB.ColC
AND tableA.ColD=tableB.ColD
AND tableA.ColE=tableB.ColE
[Results]:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tableA | ALL | (null) | (null) | (null) | (null) | 1 | |
| 1 | SIMPLE | tableB | ALL | (null) | (null) | (null) | (null) | 1 | Using where; Using join buffer |
Query 2:
EXPLAIN
SELECT
tableA.ColA
,tableA.ColB
,tableA.ColC
,tableA.ColD
,tableA.ColE
FROM tableA
JOIN tableB ON tableA.ColA=tableB.ColA
AND tableA.ColB=tableB.ColB
AND tableA.ColC=tableB.ColC
AND tableA.ColD=tableB.ColD
WHERE
tableA.ColE=tableB.ColE
[Results]:
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tableA | ALL | (null) | (null) | (null) | (null) | 1 | |
| 1 | SIMPLE | tableB | ALL | (null) | (null) | (null) | (null) | 1 | Using where; Using join buffer |
You can review the full details with the following links. I also created a SQL 2008 example so that you can compare how the two engines work (which is the same):
MySQL query example
SQL 2008 query example (Make sure you 'View Execution Plan' for both results)
INNER JOIN
, but with aLEFT JOIN
this will return different results. Basically, the conditions that you added on theWHERE
on your second query are converting yourJOIN
on anINNER JOIN
– Lamak Feb 1 '13 at 19:54INNER JOIN
do my questions on performance remain valid? – Geoff Feb 1 '13 at 19:57ON
and filter criteria in theWHERE
. – Aaron Bertrand Feb 1 '13 at 19:59