Full Join diff left + right joint

Question

If I have 2 Tables: T1 and T2 and I do something like this:

select ISNULL(T1.Name, T2.Name) AS Name
from T1
FULL JOIN T2 ON T1.Product = T2.Product

And this join:

select ISNULL(ISNULL(T1.Name, T2.Name),T3.Name) AS Name
from T1
Left JOIN T2 ON T1.Product = T2.Product
Right JOIN T2 T3 ON T1.Product = T3.Product

They are same or some cases are different?

*"They are same or some cases are different?"* Why not run the code you have, and find out? I can tell you, however, that the queries are *not* the same from a processing perspective. — Thom A, Dec 24 '20 at 11:18

score 3 · Answer 1 · answered Dec 24 '20 at 11:33

Both queries do not produce the same results.

The first query returns all names from both tables. The second query only return names that are in t2, because of the final right join.

Consider this simple dataset:

insert into t1 (name, product) values ('foo1', 'bar'), ('baz1', 'zoo1');
insert into t2 (name, product) values ('foo2', 'bar'), ('baz2', 'zoo2');

Both tables have one product in common, and one product on their own.

The first query correctly returns foo1, baz1 and baz2, while the second query returns just foo1 and baz2.

Unrelated note: in general, use coalesce() instead of isnull(); that's standard SQL, and it accepts more than two arguments.

1 Answers1