CODEWITHSUNDEEP
sqloracle-databaseleft-join
Cosmin Cosmin
Cosmin Cosmin

Reputation: 1556

Is there a difference between these 2 left joins in Oracle SQL? and why

SELECT * 
FROM TAB1 
LEFT JOIN TAB2 ON TAB2.ID_TAB1 = TAB1.ID 
JOIN TAB3 ON TAB3.ID = TAB2.ID_TAB3;

and 

SELECT *
FROM TAB1
LEFT JOIN (SELECT *
           FROM TAB2
           JOIN TAB3 ON TAB3.ID = TAB2.ID_TAB3) T
ON T.ID_TAB1 = TAB1.ID;

Upvotes: 3

Views: 302

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

What you might want is:

SELECT * 
FROM TAB1 
LEFT JOIN TAB2
JOIN TAB3
 ON TAB3.ID = TAB2.ID_TAB3
 ON TAB2.ID_TAB1 = TAB1.ID;

In your first query, after you have the LEFT JOIN and it's associated ON clause, then the next JOIN (of any kind) is against that entire constructed potential result set (i.e. the already joined TAB2 and TAB1). And that next join being an INNER JOIN, it must be satisfied completely for the join to complete.

Whereas in the above query, I've introduced a LEFT JOIN, but don't introduce it's associated ON clause until after I've completed a further join on the "right hand side" of the left join.

Just has to download, install Oracle Express. This script:

CREATE TABLE TAB1 (
    ID int not null
);
CREATE TABLE TAB2 (
    ID_TAB1 int not null,
    ID_TAB3 int not null
);
CREATE TABLE TAB3 (
    ID int not null
);
INSERT INTO TAB1 (ID) VALUES (1);
INSERT INTO TAB1 (ID) VALUES (2);
INSERT INTO TAB3 (ID) VALUES (1);
INSERT INTO TAB3 (ID) VALUES (2);
INSERT INTO TAB2 (ID_TAB1,ID_TAB3) VALUES (2,2);
SELECT * 
FROM TAB1 
LEFT JOIN TAB2
JOIN TAB3
 ON TAB3.ID = TAB2.ID_TAB3
 ON TAB2.ID_TAB1 = TAB1.ID;

Worked, and ran as expected.

It might be easier to think about what this query does if you think of JOIN as introducing a left parenthesis (, and ON introducing a right parenthesis ). Then, the JOINs and ON are matched in the same way that parenthesis are usually matched.

Or, another equal way, would be to consider each join clause to be "open" until its corresponding ON is encountered. But the rule for matching an ON to a JOIN is to find the closest "open" JOIN.

Either way, in the above query, the first ON clause satisfies the join between TAB2 and TAB3. The second ON clause then satisfies the LEFT JOIN between TAB1 and the joined (TAB2 and TAB3).

Upvotes: 2

user359040
user359040

Reputation:

No, they are different - the inner join to TAB3 (after the left join to TAB2) in the first query effectively turns the left join back into an inner join.

The brackets in the second query ensure that the inner join to TAB3 is evaluated before the left join - so it remains a left join to TAB2, returning only those TAB2 records where there is a corresponding TAB3 record (otherwise, the TAB1 records are returned with corresponding NULLs).

Upvotes: 9

Related Questions