outer join more than 1 table

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','[email protected]');
INSERT INTO T5 VALUES (44,'john', 'doe','[email protected]');
INSERT INTO T5 VALUES (22,'jeff', 'bush','[email protected]');
INSERT INTO T5 VALUES (44,'jeff', 'bush','[email protected]');

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  | [email protected]
jeff       | bush      |  1500  | [email protected]
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.

Solution

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 | [email protected] |
|       jeff |      bush |   1500 | [email protected] |
|       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:

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.}}