Why does using the "AND" operator works with a JOIN, even without a "WHERE" clause

Question

Good afternoon, I just noticed something. If I write a mysql query with a JOIN in it, for example:

Select Car, Vendor from Cars JOIN Vendor On Vendor.IdCar=Car.IdCar WHERE  Car="Brand"

It will return that car that is equal to "Brand".

But I noticed that if I structure the query this way:

Select Car, Vendor from Cars JOIN Vendor On Vendor.IdCar=Car.IdCar AND Car="Brand" 

It will return the same thing as the previous query. Noticed that I used AND instead of WHERE. I also tested in PHP queries it worked. So for example:

Select Car, Vendor from Cars JOIN Vendor On Vendor.IdCar=Car.IdCar AND Car=$brand

instead of

Select Car, Vendor from Cars JOIN Vendor On Vendor.IdCar=Car.IdCar WHERE Car=$brand

So the question I'm asking is: Why does the second query work? Is it because JOIN was used, because I was pretty sure that WHERE clause was needed before AND?

Answer 1

Because a JOIN is not restricted to match on primary keys; and even if it was, there are composite keys which require multiple columns to match, so you definitely need AND there.

The statement you show is not good style, because it abuses the join clause to emulate what should rather be a where condition; yet, it will work as desired.

The statement could definitely make sense if you were to use an outer join. In that case, the boolean expression in the join clause is not functionally equivalent to the same boolean expression in the where clause. Hence, depending on what you want to query for with an outer join, you must use one or the other.

Answer 2

Both WHERE and ON require a (boolean) condition. And a boolean condition can be constructed from boolean terms by combining them with AND, OR and NOT(and possibly ( and ) )

• WHEREcondition is an (optional) clause in a query
• table-expression JOIN table-expression ON condition is an operator that combines two table-expressions, based on the condition.