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
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.
source to share
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).
source to share
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.
source to share