topic Re: Will these two queries yield same result? in SAS Programming

Will these two queries yield same result?

vkumbhakarna — Fri, 10 Apr 2020 22:27:36 GMT

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;

Re: Will these two queries yield same result?

PaigeMiller — Fri, 10 Apr 2020 21:36:30 GMT

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

Re: Will these two queries yield same result?

Kurt_Bremser — Sat, 11 Apr 2020 11:12:46 GMT

See Maxim 4.

In short, try it.

Re: Will these two queries yield same result?

s_lassen — Sat, 11 Apr 2020 14:34:56 GMT

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.

Re: Will these two queries yield same result?

ChrisNZ — Sun, 12 Apr 2020 00:50:20 GMT

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

Re: Will these two queries yield same result?

vkumbhakarna — Sun, 12 Apr 2020 17:56:15 GMT

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;

Re: Will these two queries yield same result?

s_lassen — Tue, 14 Apr 2020 07:02:21 GMT

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