1> /* Advanced SQL Programming Fall 2002 */
2> /* */
3> /* 10/21/02: Question about equivalent outer joins */
--6. (From an Internet discussion group)
--
--Suppose I have three tables TNL1, TNL1_1 and TNL1_2 all with a column
--named ID, are the following queries the same ?
--
--SELECT * FROM TNL1 A LEFT OUTER JOIN (TNL1_1 B LEFT OUTER JOIN TNL1_2
--C ON B.ID=C.ID) ON A.ID=B.ID ORDER BY 1
--
--and
--SELECT * FROM TNL1 A LEFT OUTER JOIN TNL1_1 B ON A.ID=B.ID LEFT OUTER
--JOIN TNL1_2 C ON B.ID=C.ID ORDER BY 1
--
--and
--SELECT * FROM (TNL1 A LEFT OUTER JOIN TNL1_1 B ON A.ID=B.ID) LEFT
--OUTER JOIN TNL1_2 C ON B.ID=C.ID ORDER BY 1
--
5> /* */
6> /* Test tables created to mimic those in original question. */
7> /* Some data present in all tables, some only in first or second*/
8> /* */
9> use evelyn
1> /* */
2> select * from jointest1
ID
-----------
1
2
3
4
5
6
(6 rows affected)
1> /* */
2> select * from jointest2
ID
-----------
1
2
3
4
(4 rows affected)
1> /* */
2> select * from jointest3
ID
-----------
1
2
(2 rows affected)
1> /* */
2> /* For second left join statement (no parenthesized grouping) */
3> /* use sequential left outer joins on the three tables. */
4> /* */
5> select *
6> from jointest1 a, jointest2 b, jointest3 c
7> where a.ID *= b.ID
8> and b.ID *= c.ID
ID ID ID
----------- ----------- -----------
1 1 1
2 2 2
3 3 NULL
4 4 NULL
5 NULL NULL
6 NULL NULL
(6 rows affected)
1> /* */
2> /* Cannot write equivalent grouped outer joins in Sybase SQL: */
3> /* Create views that "left-join" two tables at a time and then */
4> /* do a left join between each view and the remaining table. */
5> /* */
6> /* View definitions and contents: */
7> /* */
8> --
9> -- View on jointest1 and jointest2 with left outer join
10> --
11> sp_helptext jointest12
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create view jointest12
as
select a.ID ID1, b.ID ID2
from jointest1 a, jointest2 b
where a.ID *= b.ID
(1 row affected)
(return status = 0)
1> /* */
2> select * from jointest12
ID1 ID2
----------- -----------
1 1
2 2
3 3
4 4
5 NULL
6 NULL
(6 rows affected)
1> /* */
2> --
3> -- View on jointest2 and jointest3 with left outer join
4> --
5> sp_helptext jointest23
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create view jointest23
as
select b.ID ID2, c.ID ID3
from jointest2 b, jointest3 c
where b.ID *= c.ID
(1 row affected)
(return status = 0)
1> /* */
2> select * from jointest23
ID2 ID3
----------- -----------
1 1
2 2
3 NULL
4 NULL
(4 rows affected)
1> /* */
2> /* First test: jointest1 with jointest23 */
3> /* */
4> select * from jointest1, jointest23
5> where ID *= ID2
ID ID2 ID3
----------- ----------- -----------
1 1 1
2 2 2
3 3 NULL
4 4 NULL
5 NULL NULL
6 NULL NULL
(6 rows affected)
1> /* */
2> /* Second test: jointest12 with jointest3 */
3> /* */
4> select * from jointest12, jointest3
5> where ID1 *= ID
ID1 ID2 ID
----------- ----------- -----------
1 1 1
2 2 2
3 3 NULL
4 4 NULL
5 NULL NULL
6 NULL NULL
(6 rows affected)
1> /* Repeat "straight-through" join for comparison: */
2> /* */
3> select *
4> from jointest1 a, jointest2 b, jointest3 c
5> where a.ID *= b.ID
6> and b.ID *= c.ID
ID ID ID
----------- ----------- -----------
1 1 1
2 2 2
3 3 NULL
4 4 NULL
5 NULL NULL
6 NULL NULL
(6 rows affected)
1> /* */
2> dump tran evelyn with truncate_only