Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is my t-sql left join not working?

Tags:

sql

sql-server

Can you run this and tell me why the result set only has two rows. It should have three and look like this...

appId    stepId       section       start
101      1           Section 1     2016-01-03 00:00:00.000
101      2           Section 2     2016-01-03 00:00:00.000
101      10          Section 3     NULL

Here is the sql so you can just paste it into your query tool

create table #appSteps(stepId decimal, section nvarchar(50))
insert into #appSteps (stepId, section) values (1, 'Section 1')
insert into #appSteps (stepId, section) values (2, 'Section 2')
insert into #appSteps (stepId, section) values (3, null)
insert into #appSteps (stepId, section) values (4, null)
insert into #appSteps (stepId, section) values (10, 'Section 3')

create table #appProgress(stepId decimal, appId int, start datetime)
insert into #appProgress (stepId, appId, start) values (1, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (2, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (3, 101, '1/3/2016')
insert into #appProgress (stepId, appId, start) values (4, 101, '1/3/2016')


select p.appId, s.stepId, s.section, p.start
from #appSteps s with (nolock)
left join #appProgress p on s.stepId = p.stepId
where s.section is not null
and p.appId = 101

drop table #appSteps
drop table #appProgress

I cannot figure out why all 3 non null rows from #appSteps are not coming back

like image 752
Roto Avatar asked Oct 17 '16 19:10

Roto


People also ask

Why is my left join not returning nulls?

Left join, right join, any old join at all that is not an outer join will return only the rows that match the join criteria. Nulls will not be generated for the non matching rows because those rows will not be returned.

What is SQL join (+) means?

An SQL Join is an operation that combines records from two or more tables. This is done by matching keys between tables.

How LEFT join will work in SQL?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

How LEFT join is executed?

SQL Left Join Syntaxcoumn_x = table2. column_y; where SELECT, LEFT JOIN, and ON are the keywords, columns are the list of columns, table1 is the first table and table2 is the second table, and column_x and column_y are the columns for performing the left join, followed by a semicolon.


1 Answers

The reason is because you are including the right-hand table in the WHERE clause. You should move that to the ON condition of the LEFT JOIN:

Select    P.appId, S.stepId, S.section, P.start
From      #appSteps    S   With (NoLock)
Left Join #appProgress P   On  S.stepId = P.stepId 
                           And P.appId = 101
Where     S.section Is Not Null

The reason it does this is because the WHERE clause is evaluated after the LEFT JOIN, which then filters out your NULL results from the LEFT JOIN.

Including the right-hand table of a LEFT JOIN (or the left-hand table of a RIGHT JOIN) in the WHERE clause effectively transforms the OUTER JOIN into an INNER JOIN.

like image 145
Siyual Avatar answered Oct 14 '22 23:10

Siyual