Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
No, you are not right saying that. It would be if that were an INNER JOIN, but with a LEFT JOIN this will return different results. Basically, the conditions that you added on the WHERE on your second query are converting your JOIN on an INNER JOIN –  Lamak Feb 1 '13 at 19:54
    
Ah ok. I follow what you say. If I edit to INNER JOIN do my questions on performance remain valid? –  Geoff Feb 1 '13 at 19:57
2  
For INNER JOINs there should be no difference in performance. That said, for readability and proper expression of intent, you should use join criteria in the ON and filter criteria in the WHERE. –  Aaron Bertrand Feb 1 '13 at 19:59
    
@ypercube right, I missed that condition. –  Lamak Feb 1 '13 at 20:46

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
Thank you for your detailed solution. I tried INNER JOIN instead of LEFT JOIN and I get the same output in a tenth of the time. I think I know why I get the same output, but why would an INNER JOIN have better performance? –  Geoff Feb 1 '13 at 20:35
2  
As LEFT JOIN is an outer join, it can't restrict the data set on the full return side of the set and will try to retrieve all rows from that table (in this case, TableA). If you use INNER JOIN, it can leverage that criteria on both tables and restrict the data set, thus providing a faster return. –  Mike Fal Feb 1 '13 at 20:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.