CODEWITHSUNDEEP
phpmysqljoinwhere-clause
coder_1432
coder_1432

Reputation: 283

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

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?

Upvotes: 4

Views: 70

Answers (4)

JimmyB
JimmyB

Reputation: 12610

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.

Upvotes: 4

wildplasser
wildplasser

Reputation: 44250

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.

Upvotes: 2

HoneyBadger
HoneyBadger

Reputation: 15140

join is short for inner join, so you are filtering the join on all conditions in the on clause. This has the same effect as placing the condition in the where clause. If you use an outer join (left or right), you'd get all records of the table on the left or right side of the join, and null values for those records that don't match the condition, while a where clause would still only select those records that do match the condition.

Upvotes: 1

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

It is for readability purpose so you can place the on clause in the where clause and things will be done correctly:

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

And this is valid also:

Select Car, Vendor from Cars, Vendor where Vendor.IdCar=Car.IdCar AND Car="Brand";

But it can't do outer join(left join or right join)

Upvotes: 0

Related Questions