CODEWITHSUNDEEP
mysql
cm94
cm94

Reputation: 43

Mysql join: Where to put the condition that goes on the right table?

I read somewhere that the condition on the right table(j.jobStatus="A") must place before the where clause.

Is there a difference betwen 1) and 2)?

1)

SELECT c.cmpID
FROM company AS c
LEFT JOIN jobs AS j ON c.jobID=j.jobID
WHERE j.jobStatus="A" AND c.cmID > 10

2)

SELECT c.cmpID
FROM company AS c
LEFT JOIN jobs AS j ON c.jobID=j.jobID AND j.jobStatus="A"
WHERE c.cmID > 10

Upvotes: 3

Views: 51

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191789

The difference is that WHERE j.jobStatus = "A" nullifies the LEFT JOIN, essentially making it an INNER JOIN. Any rows where j would be NULL are filtered out by that condition.

If the condition is on the ON clause, it still allows NULL rows to be returned for the jobs table (i.e. rows in the company table that do not have a matching row in the jobs table).

Upvotes: 1

Related Questions