Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
CREATE TEMPORARY TABLE tmp_userid
(
    uUserId int unsigned NOT NULL PRIMARY KEY
) ENGINE='MEMORY';

CREATE TEMPORARY TABLE tmp_studentid
(
    uClassId INT UNSIGNED NOT NULL,
    uUserId int unsigned NOT NULL,
    PRIMARY KEY(uClassId,uUserId)
) ENGINE='MEMORY';

INSERT INTO tmp_userid
values (18);

INSERT INTO tmp_studentid
values (1,1),
    (1,3),
    (2,15),
    (3,20),
    (3,25),
    (4,35),
    (4,45)
;

Now, if I try to execute:

SELECT * FROM tmp_userid U
LEFT JOIN tmp_studentid S ON U.uUserId = S.uUserId
where  uClassId = 4;

SELECT * FROM tmp_userid U
LEFT JOIN tmp_studentid S ON U.uUserId = S.uUserId AND uClassId = 4;

returns different result!

Any one can explain this behavior?

Thanks

share|improve this question

closed as off topic by EEAA, pauska, Ward, Ladadadada, Michael Hampton Aug 29 '12 at 23:21

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
"explain" is exactly what you should be doing. –  rackandboneman May 17 '12 at 14:45
1  
Disagree - the meaning of the SQL is not the purpose of EXPLAIN it merely shows the execution plan. In this case matang has not understood why the queries behave differently (it's because they are asking different questions) –  symcbean May 17 '12 at 15:29

1 Answer 1

up vote 3 down vote accepted

if you check execution plans with EXPLAIN you'll see that first query has Impossible WHERE noticed after reading const tables. T hat's it - mysql does JOINs first, and then filters the result. Since no join output row matches your filter you get empty end result.

The second query does an OUTER JOIN with a condition that is always false, returning NULL for uClassId and uUserId, but since there's no post-join filtering you get results.

share|improve this answer

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