What is the difference between these 2 queries

Question

--query 1

select distinct af.Code from AIR af inner join
Float spf on spf.Station = af.AirID or
spf.DeptStation = af.AirID

--query 2

select distinct af.Code from AIR af 
    inner join Float spf on spf.Station = af.AirID
    inner join Float spf1 on spf.DeptStation = af.AirID

In the first query i get a few more entries (around 10) when compared to second. As per me, this difference should not be there. If someone can break down the queries to tell me the difference (if any) it will be helpful.

http://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea — Tim Schmelter, Jun 20 '13 at 10:31
The first uses `OR` whereas the second is similar to an `AND`. So the first query returns all records with a matching station **or** a matching dept-station. The second is more strict since it returns only records where both match. So it's similar to (as Bastos wrote) `inner join Float spf on spf.Station = af.AirID and spf.DeptStation = af.AirID`. — Tim Schmelter, Jun 20 '13 at 10:39

score 0 · Answer 1 · answered Jun 20 '13 at 10:32

0

this query:

select distinct af.Code from AIR af 
inner join Float spf on spf.Station = af.AirID
inner join Float spf1 on spf1.DeptStation = af.AirID

is equal to an "and" join

select distinct af.Code from AIR af
inner join Float spf on spf.Station = af.AirID and spf.DeptStation = af.AirID

Edit You had a error on your second query. It should be

inner join Float spf1 on spf1.DeptStation = af.AirID

answered Jun 20 '13 at 10:32

bastos.sergio

6,684
4
26
36

well equal to in results, not in execution... :) – bastos.sergio Jun 20 '13 at 10:34
I dont agree - with the `and` logic i am getting 5 more rows compared to `query 2` – Expert Novice Jun 20 '13 at 10:38

gbn · Answer 2 · 2013-06-25T09:22:29.153

In the 2nd query, you match one AirID pre row to 2 different values at the same time (AND) So, the JOIN only gives results where both match which is of course nonsense.

In the first query, you match one AirID to one of 2 different columns (OR) So, you get correct results because when it matches either value

You can rewrite the first one as this which may help to demonstrate the OR-ness

select af.Code from AIR af 
    inner join Float spf on spf.Station = af.AirID
UNION
select af.Code from AIR af 
    inner join Float spf on spf.DeptStation = af.AirID

What is the difference between these 2 queries

2 Answers2