Will these two queries yield same result?

[vkumbhakarna]

Will query A and query B yield same result below?

Will these two queries yield same results even if the data changes? Is the left outer join along with where condition in Query A is equivalent to the inner join in Query B for all cases of data. Are there any cases for which the two queries will not be identical? Can Query A be replaced with Query B to get same results?

Please advise.

Query A

proc sql;
select a.key, a.val1, b.val2, c.val3
from a left join b
On a.key=b.key
left join c
on a.key=c.key
where b.val2=20 and c.val3=30
;
quit;

Query B

proc sql;
select a.key, a.val1, b.val2, c.val3
from a inner join b on a.key=b.key and b.val2=20
inner join c on a.key=c.key and c.val3=30
;
quit;

Data:

Sample data below, you may change cases to break the query if you need to.

data a;
length key $1. val1 8.;
input key val1;
datalines;
a 0
a 10
b 10
b 20
c 30
d 40
e 50
;
run;

data b;
length key $1. val2 8.;
input key val2;
datalines;
b 20
b 10
c 10
d 20
e 20
f 20
g 10
g 20
;
run;

data c;
length key $1. val3 8.;
input key val3;
datalines;
a 10
b 20
b 30
c 20
c 30
c 40
d 30
e 50
h 20
h 30
;
run;

[PaigeMiller]

What happens if your run the two different codes on the data?

[Kurt_Bremser]

See Maxim 4.

In short, try it.

[s_lassen · Accepted solution]

The two queries will yield the same results.

Although one is a left join and the other is an inner join, any additional rows of ID values in table A, but not in C or B, will not be included in the left join because of the WHERE clause applied to the left join, which will only allow rows with data from B and C.

Anyway, with the data you have presented there are no ID values in table A which are not in the other two tables, so using a left join will not make any difference here in any case.

[vkumbhakarna]

Thank you! I updated the data to test this.  I tested the scenario by adding 'f' to A and B and not in C. The where condition transforms the left join to inner join. Although, the left join with where condition would be a costlier (in terms of processing) as compared to the inner join because more  rows will be pulled in and later eliminated by where condition. So the inner join will be more efficient, i guess (in this particular scenario). 

/*Query A*/

proc sql;
select a.key, a.val1, b.val2, c.val3
from a left join b
On a.key=b.key
left join c
on a.key=c.key
where b.val2=20 and c.val3=30
;
quit;

/*Query B*/

proc sql;
select a.key, a.val1, b.val2, c.val3
from a inner join b on a.key=b.key and b.val2=20
inner join c on a.key=c.key and c.val3=30
;
quit;

/*Data*/

data a;
length key $1. val1 8.;
input key val1;
datalines;
a 0
a 10
b 10
b 20
c 30
d 40
e 50
f 70
;
run;

data b;
length key $1. val2 8.;
input key val2;
datalines;
b 20
b 10
c 10
d 20
e 20
f 20
g 10
g 20
f 20
;
run;

data c;
length key $1. val3 8.;
input key val3;
datalines;
a 10
b 20
b 30
c 20
c 30
c 40
d 30
e 50
h 20
h 30
;
run;

[ChrisNZ]

The WHERE clause makes it compulsory that you have data coming from tables B and C, thereby transforming the left joins into inner joins.

[s_lassen]

Yes, but @vkumbhakarna may be right that the inner join query could be more efficient. Depends on the quality of the SQL interpreter.