T-SQL Question/Explanation

Transact-SQL
1

Guys,
Here are my two queries.

  1. Select * from TableA
    Left Outer Join TableB on TableA.ID = TableB.ID
    Where TableB.Value > 1000

  2. Select * from TableA
    Left Outer Join TableB on TableA.ID = TableB.ID
    and TableB.Value > 1000

My question is by looking at which query will return more records and why?

My Thoughts:- As per my understanding Query # 2

Explanation Query #1:- Let's break the first query in two sections.
a) The first section until Left Outer join.
b) WHERE Clause filter use from Left Outer Join result.
Left Outer Join gives all value from TableA and Matching records from TableB
and only return all records where TableB.Value > 1000

Explanation Query #2:- Let's break the second query in two sections as well.
a) The first section until Left Outer Join.
b) AND operator remove records from TABLE B
Left Outer Join gives all value from Table A and brings only all records from Table B where Value > 1000

Please correct me if I am wrong and help me out to understand.

Thank you in advance.

3

Hi harishgg1,

Thank you for your reply. Yes it is make sense but I want to know the explanation why, Is my explanation make sense?

4

Those are the same identical queries to my feeble eyes.

Never mind, more like blind as a bat

5

hi

i tried to do some testing

in my tests both returned ... same amount of rows

please click arrow mark to the left for "drop create data" 
drop table TableA
go 

create table TableA
(
id int 
)
go 

drop table TableB
go 

create table TableB
(
id int ,
value int 
)
go 

insert into TableA select 1
insert into TableB select 1,1002
insert into TableB select 1,1500
insert into TableB select 1,200
insert into TableB select 1,10
go 

insert into TableA select 2
insert into TableB select 2,10
insert into TableB select 2,2000
insert into TableB select 2,1300
insert into TableB select 2,102
go 

select * from TableA
go 

select * from TableB
go
please click arrow mark to the left for "SQL" .. 
SELECT * 
FROM   tablea 
       LEFT OUTER JOIN tableb 
                    ON tablea.id = tableb.id 
WHERE  tableb.value > 1000 

SELECT * 
FROM   tablea 
       LEFT OUTER JOIN tableb 
                    ON tablea.id = tableb.id 
                       AND tableb.value > 1000

image
image.png537×1082 155 KB

6

Hi Harishgg1,

Thanks for your effort to create a sample data. The reason you are getting a same records from both queries because all matching IDs from TableA exists in Table B. I just added more IDs in Table A that not in Table B, Because its a "Left Outer Join" that's why return everything from Left table.

Thank you.

SQL Statement

drop table TableA
go

create table TableA
(
id int
)
go

drop table TableB
go

create table TableB
(
id int ,
value int
)
go

insert into TableA select 1
insert into TableA select 3
insert into TableB select 1,1002
insert into TableB select 1,1500
insert into TableB select 1,200
insert into TableB select 1,10
go

insert into TableA select 2
insert into TableA select 4
insert into TableB select 2,10
insert into TableB select 2,2000
insert into TableB select 2,1300
insert into TableB select 2,102
go

select * from TableA
go

select * from TableB
GO

SELECT *
FROM tablea
LEFT OUTER JOIN tableb
ON tablea.id = tableb.id
WHERE tableb.value > 1000

SELECT *
FROM tablea
LEFT OUTER JOIN tableb
ON tablea.id = tableb.id
AND tableb.value > 1000

7

The 2nd query will typically return more rows, since it will return ALL TableA rows, no matter what is in TableB. This is the correct way to code a LEFT JOIN.

The 1st query will return rows ONLY if TableB.Value > 1000, meaning that if a matching B row is missing, the A row will NOT be returned. This is the incorrect way to code a LEFT JOIN.

Remember that on a LEFT JOIN, if a row is missing in the left table, all columns in that table become NULL. Thus, in the 1st query, if the LEFT JOIN does not find a match in TableB, then TableB.Value is set to NULL. When the WHERE condition checks the Value column, NULL is never =, <>, < or > any value; the WHERE condition fails, and the row is rejected.