outer join more than 1 table

Question

let's say I have 5 tables, as follows:

CREATE TABLE T1 (
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
CITY NUMERIC,
SALARY NUMERIC);

CREATE TABLE T2 (
CITY NUMERIC,
DISTRICT  NUMERIC);

CREATE TABLE T3 (
DISTRICT NUMERIC,
DOMAIN NUMERIC);

CREATE TABLE T4 (
DOMAIN NUMERIC,
DETAILS_BOOK NUMERIC);

CREATE TABLE T5 (
DETAILS_BOOK NUMERIC,
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
EMAIL VARCHAR2(100));

INSERT INTO T1 VALUES ('john', 'doe',1001,1000); 
INSERT INTO T1 VALUES ('jack', 'jill',1001,2000);
INSERT INTO T1 VALUES ('jeff', 'bush',1001,1500);

INSERT INTO T2 VALUES (1001,1);

INSERT INTO T3 VALUES (1,543);

INSERT INTO T4 VALUES (543,22);

INSERT INTO T5 VALUES (22,'john', 'doe','john@22.com');
INSERT INTO T5 VALUES (44,'john', 'doe','john@44.com');
INSERT INTO T5 VALUES (22,'jeff', 'bush','jeff@22.com');
INSERT INTO T5 VALUES (44,'jeff', 'bush','jeff@44.com');

now, I want all records from t1, with their salaries and emails, corresponding to tables t2, t3, and t4, such that the reuslt should be:

FIRST_NAME | LAST_NAME | SALARY | EMAIL
--------------------------------------------------
john       | doe       |  1000  | john@22.com
jeff       | bush      |  1500  | jeff@22.com
jack       | jill      |  2000  | (NULL)

what I got so far is:

SELECT T1.FIRST_NAME, T1.LAST_NAME,T1.SALARY,T5.EMAIL
FROM T1,T2,T3,T4,T5
WHERE   T1.FIRST_NAME = T5.FIRST_NAME (+)
and     T1.LAST_NAME = T5.LAST_NAME(+)
AND     T1.CITY = T2.CITY
AND     T2.DISTRICT = T3.DISTRICT
AND     T3.DOMAIN = T4.DOMAIN
AND     T4.DETAILS_BOOK = T5.DETAILS_BOOK

which returns only the first two rows.

Answer 1

From Oracle 18 (and maybe Oracle 12, but I don't have an instance to test against) you can use:

SELECT  T1.FIRST_NAME,
        T1.LAST_NAME,
        T1.SALARY,
        T5.EMAIL
FROM    T1,
        T2,
        T3,
        T4,
        T5
WHERE   T1.FIRST_NAME   = T5.FIRST_NAME   (+)
AND     T1.LAST_NAME    = T5.LAST_NAME    (+)
AND     T4.DETAILS_BOOK = T5.DETAILS_BOOK (+)
AND     T1.CITY         = T2.CITY
AND     T2.DISTRICT     = T3.DISTRICT
AND     T3.DOMAIN       = T4.DOMAIN

or, equivalently, using ANSI joins (which are considered better practice than comma joins):

SELECT  T1.FIRST_NAME,
        T1.LAST_NAME,
        T1.SALARY,
        T5.EMAIL
FROM    T1
        INNER JOIN T2 ON T1.CITY         = T2.CITY
        INNER JOIN T3 ON T2.DISTRICT     = T3.DISTRICT
        INNER JOIN T4 ON T3.DOMAIN       = T4.DOMAIN
        LEFT OUTER JOIN T5
                      ON T1.FIRST_NAME   = T5.FIRST_NAME
                     AND T1.LAST_NAME    = T5.LAST_NAME
                     AND T4.DETAILS_BOOK = T5.DETAILS_BOOK

Which both output:

FIRST_NAME	LAST_NAME	SALARY	EMAIL
john	doe	1000	john@22.com
jeff	bush	1500	jeff@22.com
jack	jill	2000	null

Oracle 18 Fiddle

---

In Oracle 11g, the first query gives the exception:

ORA-01417: a table may be outer joined to at most one other table

But the second query (with the ANSI joins) still works.

Oracle 11g Fiddle

Answer 2

Try this instead:

SELECT 
  T1.FIRST_NAME, 
  T1.LAST_NAME,
  T1.SALARY,
  T5.EMAIL
FROM T1
LEFT JOIN T2  ON T1.CITY         = T2.CITY
LEFT JOIN T3  ON T2.DISTRICT     = T3.DISTRICT
LEFT JOIN T4  ON T3.DOMAIN       = T4.DOMAIN
LEFT JOIN T5  ON T4.DETAILS_BOOK = T5.DETAILS_BOOK
             AND T1.FIRST_NAME   = T5.FIRST_NAME
             AND T1.LAST_NAME    = T5.LAST_NAME;

SQL Fiddle Demo

This will give you:

| FIRST_NAME | LAST_NAME | SALARY |       EMAIL |
-------------------------------------------------
|       john |       doe |   1000 | john@22.com |
|       jeff |      bush |   1500 | jeff@22.com |
|       jack |      jill |   2000 |      (null) |

The problem is that the INNER JOIN after the OUTER JOIN makes your joins works like an INNER , because, the inner joins eliminate those unmatched rows coming from the outer joins.

Note that: I used the ANSI SQL-92 explicit LEFT OUTER JOIN syntax, instead of the old implicit OUTER and INNER join syntax that you sued in your query.

Please try to use the LEFT OUTER JOIN instead of the old outer join syntax, and avoid INNER JOIN after OUTER JOINs.

For more details, see these:

• Bad habits to kick : using old-style JOINs..

• Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

---

Update:

When you have many tables references in the FROM clause with the JOIN between them, each table is joined with the next table begging from the FROM clause¹, results a temporary result set, then this temporary result set is joined with the next table and so on. In case of the OUTER JOIN, there are left or right:

• LEFT JOIN will include those unmatched rows from the left table, where as,
• RIGHT JOIN will include those unmatched rows from the right table.

Depending on the data you want to select, you have to watch out those tables in the two sides of the JOIN operator and the order of them.

---

^{1:This is just the logical query processing order, but in the actual order is always up to the query optimizer.}