CODEWITHSUNDEEP
sql
kadina
kadina

Reputation: 5372

confusion with multiple conditions in where clause of select statement

I am new to sql. I have the following tables in the database.

table : person
columns : id, first_name, last_name, age
values : 0, 'Zed', 'Shaw', 50

table : pet
columns : id, name, age, dead
values : 0, 'Dog', 2, 0
         1, 'Cat', 2, 1

table : person_pet
columns : person_id, pet_id
values : 0, 0
         0, 1

Now I need to find what pets Zed owns. I have written select statement to find the pets owned by Zed as given below.

SELECT pet_id, pet_name, pet_age, pet_dead FROM person, pet, person_pet WHERE person.first_name = 'Zed' and person.id = person_pet.person_id and pet.id = person_pet.pet_id;

where the author has written like below.

SELECT pet_id, pet_name, pet_age, pet_dead FROM person, pet, person_pet WHERE pet.id = person_pet.pet_id AND person_pet.person_id = person.id AND person.first_name = 'Zed';

Both SELECT statements are working fine. So It says that order of the conditions in WHERE clause doesn't matter. Is my understanding correct and is there any performance difference of the 2 SELECT statements?

Upvotes: 0

Views: 53

Answers (1)

juergen d
juergen d

Reputation: 204884

The order in the where clause does not matter and will be optimized by the query compiler.

But nowadays the explicit join syntax is used:

SELECT pet_id, pet_name, pet_age, pet_dead 
FROM person
JOIN person_pet ON person.id = person_pet.person_id
JOIN pet ON pet.id = person_pet.pet_id
WHERE person.first_name = 'Zed'

That way you can differ between table linking in the on clause and real data filtering in the where clause. And you can use different joins than the default inner join.

And by the way - don't use 0 als id. It starts normally from 1.

Upvotes: 4

Related Questions