Which is better: many join conditions or many where conditions?

Question

I am trying to compare two queries:

Query 1:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  AND tableA.e=tableB.e 

Query 2:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a AND tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  
WHERE tableA.e=tableB.e 

Am I right to say that the these two queries give the same results?

Further, is it correct to say that the first query builds a bigger table for which to do a bigger WHERE condition; whereas the second case we have a smaller constructed table to which the simple WHERE is then applied.

Assuming the results are the same, which query should be preferred? Is there an obvious performance issue?

Answer 1

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)