Clever Geek Handbook

Why does the left join go into an inner join if the inner join is included in the request?

    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 a request turns the whole request into an inner join. The first request is joined to TblA - (LEFT JOIN) → TblB → (INNER JOIN) → TblC = The whole request is internally connected.

The only solution I have found is combining the subquery in the left join, however I don't understand how it is different.

+3


source to share


3 answers


This is common behavior in database implementations due to the consequences of bundling attachments. This result will have a series of left joins followed by an inner join (or CROSS APPLY instead of OUTER APPLY).

To avoid this, you have already found a 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 an uncorrelated subquery, since you did not include the ATBL inside the parentheses - this means that the engine can choose a good enough concatenation strategy for it, or evaluate the entire subquery once, rather than line by line.



Another option is to change all 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)

Then you can use the WHERE clause to filter where none of the connections from B were found (i.e. I didn't find B, or I found both a and a).

+2


source


On the first query, the first join occurs first and the result is performed by an inner join to the next table (ctbl).



Where, as in the second query, the first join happens first and the result remains associated with your first table (atbl). Hope this answers

0


source


You still have a LEFT JOIN, but you are doing an INNER JOIN on ctbl, which filters out all the data. Looking at your expression, I think you are looking for a DIRECT INNER JOINING IN LEFT INLET:

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

So you make a LEFT JOIN between atbl and [INNER JOIN between btbl and ctbl]. Note that the condition between atbl and btbl is the last one and I have specifically identified the INNER JOIN a little more to make it more obvious, it is NESTED.

Hope it helps.

0


source






More articles:


All Articles