CODEWITHSUNDEEP
mysql
JMK
JMK

Reputation: 28059

I can use `AND` in place of `WHERE` in MySQL queries, is this a bug or a feature

I have found in MySQL when doing joins, I can use the word AND in place of WHERE like so:

SELECT * FROM `mySchema`.`tblOne`
LEFT JOIN `mySchema`.`tblTwo` ON `tblOne`.`FIELDX` = `tblTwo`.`FIELDY`
AND `FIELDZ` = 'FooBar';

Is this a bug or is there a feature? Are there scenarios where this could be useful?

Thanks

Upvotes: 1

Views: 62

Answers (4)

Sebas
Sebas

Reputation: 21532

your AND is for the ON clause, this doesn't have to do with a WHERE clause.

Upvotes: 4

to StackOverflow
to StackOverflow

Reputation: 124716

Note that using ON ... AND ... can give different results from ON ... WHERE:

SELECT * FROM `mySchema`.`tblOne` 
LEFT JOIN `mySchema`.`tblTwo` ON `tblOne`.`FIELDX` = `tblTwo`.`FIELDY` 
AND `FIELDZ` = 'FooBar';

If FIELDZ is in tblTwo (not clear from the above), this will return all rows from tblOne, and join matching rows from tblTwo (i.e. those which have FIELDY = tblOne.FIELDX and FIELDZ = 'FooBar')

SELECT * FROM `mySchema`.`tblOne` 
LEFT JOIN `mySchema`.`tblTwo` ON `tblOne`.`FIELDX` = `tblTwo`.`FIELDY` 
WHERE `FIELDZ` = 'FooBar';

If FIELDZ is in tblTwo, this will join on tblOne.FIELDX = tblTwo.FIELDY, then filter by`tblTwo.FIELDY = 'FooBar'. It will effectively be the same as an INNER JOIN.

Upvotes: 2

Matt Utley
Matt Utley

Reputation: 16

This works in SQL Server 2008 as well. I don't see how it would be a bug. The 'ON' portion of the JOIN is similar to a 'WHERE'. Basically what your query says is:

WHERE tblOne.FIELDX = tblTwo.FIELDY AND FIELDZ = 'FooBar';

You could use:

ON FIELDX = FIELDY AND FIELDZ = 'FooBar'

or:

ON FIELDX = FIELDY
WHERE FIELDZ = 'FooBar'

Upvotes: 0

Sirko
Sirko

Reputation: 74076

If you take a look at the documentation for JOIN (link), you will see, that after the ON keyword there is a conditional_expr.

This is the same conditional_expr as used in WHERE clauses. So basically you are able to do the same stuff after an ON like after a WHERE.

Following that, you are also able to use AND and OR, parenthesis and all other conditional operators there.

Upvotes: 3

Related Questions