Outer joins on non-preserved columns

In this blog I will try to explain the necessity of adding the outer join on already preserved rows.

There are 3 tables, MYUSER, TEST1, TEST2. MYUSER is related to TEST1 table and TEST1 table is related to TEST2 table. The requirement is to fetch all the users from MYUSER table and if present fetch the related data from the TEST1 table and related data from TEST2 table.

The query would be

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID(+)
order by 1

The point to note is that “AND TEST1.ID = TEST2.TEST2ID(+)“ condition should be with + sign as shown. This is required because the first join will return all the users even if there is no corresponding record in Test1 table. For those preserved rows, the value will be null for TEST1.ID column. For this row to be preserved when joined with TEST2 table, the + on TEST2 should be maintained.

This can be seen by the following example

MYUSER Table

USERID	NAME
1	Bill
2	Purva
3	James
5	Meij

TEST1 Table

ID	TEST1NAME
1	test1bill
2	test1purva

TEST2 Table

TEST2ID	TEST2NAME
2	test2purva
3	test2james

The query that meets the requirement is

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID(+)
order by 1

ANSI Syntax

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV
LEFT OUTER JOIN TEST1 TEST1 ON (USERV.USERID = TEST1.ID)
LEFT OUTER JOIN TEST2 TEST2 ON (TEST1.ID = TEST2.TEST2ID)
ORDER BY 1

USERID	NAME	ID	TEST1NAME	TEST2ID	TEST2NAME
1	Bill	1	test1bill
2	Purva	2	test1purva	2	test2purva
3	James
5	Meij

The query2 without outer join on the second clause is

SELECT USERV.USERID ,USERV.NAME,
TEST1.ID, TEST1.TEST1NAME,
TEST2ID , TEST2.TEST2NAME
FROM MYUSER USERV,
TEST1 TEST1,
TEST2 TEST2
WHERE USERV.USERID = TEST1.ID(+)
AND TEST1.ID = TEST2.TEST2ID
order by 1

USERID	NAME	ID	TEST1NAME	TEST2ID	TEST2NAME
2	Purva	2	test1purva	2	test2purva

To be precise, if a table is part of the outer join and the data from the table is outer joined to any other table, that condition also should be outer joined.