CODEWITHSUNDEEP
sql-servert-sqlleft-joininner-join
schizofreindly
schizofreindly

Reputation: 177

Why left join turns into inner join if inner join is included in the query?

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Atbl')
 DROP TABLE Atbl

CREATE TABLE ATbl
 (
    Id int unique,
    AName varchar(20),
 )

 GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Btbl')
    DROP TABLE Btbl

CREATE TABLE BTbl
 (
    Id int unique,
    BName varchar(20),
    ATblId int
 )

GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Ctbl')
    DROP TABLE Ctbl

CREATE TABLE CTbl
 (
    Id int unique,
    CName varchar(20),
    BTblId int
 )

GO

TRUNCATE TABLE Atbl
TRUNCATE TABLE Btbl
TRUNCATE TABLE Ctbl


INSERT INTO Atbl VALUES (1, 'Name1')
INSERT INTO Atbl VALUES (2, 'Name2')
INSERT INTO Atbl VALUES (3, 'Name3')

INSERT INTO Btbl VALUES (1, 'Name1', 2)
INSERT INTO Btbl VALUES (2, 'Name2', 3)

INSERT INTO Ctbl VALUES (1, 'Name2', 2)

select * from atbl
left join btbl on btbl.atblid=atbl.id
inner join ctbl on ctbl.btblid=btbl.id

select * from atbl
left join 
(select btbl.id, btbl.atblid from btbl
inner join ctbl on ctbl.btblid=btbl.id) a
on atbl.id=a.atblid

Sql results for both queries

Why one inner join in query turns the all query into inner join. The first query joins TblA -(LEFT JOIN)-> TblB -> (INNER JOIN) -> TblC = The whole query is inner joined.

The only solution that I found is joining a subquery in left join, however, I don't understand how it's different.

Upvotes: 7

Views: 3491

Answers (3)

Horia
Horia

Reputation: 1612

You still have an LEFT JOIN there but you do the INNER JOIN on ctbl which filters out all the data. Looking at your statement i think you're looking for a NESTED INNER JOIN within the LEFT JOIN: 

SELECT * 
FROM    atbl
        LEFT JOIN btbl 
            INNER JOIN ctbl 
                ON ctbl.btblid=btbl.id
            ON btbl.atblid=atbl.id

This way, you do a LEFT JOIN between atbl and the [ INNER JOIN between btbl and ctbl]. Notice the the condition between atbl and btbl is the last one and i've specially idented the INNER JOIN a bit more to make it more obvious it is NESTED.

Hope it helps.

Upvotes: 1

Steve Gray
Steve Gray

Reputation: 460

This is a common behaviour in database implementations, due to the implications of join nesting. A series of left joins followed by an inner join (or a CROSS APPLY instead of an OUTER APPLY) will have this outcome.

To avoid this, you've already hit on the solution:

select * from atbl
left join 
    (select btbl.id, btbl.atblid 
        from btbl
        inner join ctbl on ctbl.btblid=btbl.id) a
        on atbl.id=a.atblid

This is a non-correlated subquery, as you've not referenced ATBL inside the brackets - meaning the engine can select a reasonably good join strategy for it, or compute the entire subquery once rather than row-by-row.

Another option is to change all the table joins to left joins: 

select * from atbl
    left join btbl on btbl.atblid=atbl.id
        left join ctbl on ctbl.btblid=btbl.id
WHERE
     -- Rows where the first LEFT is not satisfied, or BOTH are satisfied.
     (btbl.atblid IS NULL OR ctbl.btblid IS NOT NULL)

You can then use the WHERE clause to filter where either neither of the joins from B onward were hit (i.e. either I didn't find a B or I found both a a B and a C).

Upvotes: 2

Ram Grandhi
Ram Grandhi

Reputation: 409

With your first query, the left join happens first and the result is performed inner join with the next table (ctbl).

Where as with the second query, the inner join happens first and the result is left joined with your first table (atbl). Hope this answers

Upvotes: 1

Related Questions