January 9, 2019 at 3:38 am
select * from tmp_1
id_a fname lname
1 a1 A1
2 a2 A2
3 a3 A3
4 aaaa4 AAAA4
select * from tmp_2
id_b fname lname
1 b1 B1
2 b2 B2
3 b3 B3
QUERIES
------------------
1. select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b and b.lname = 'B3'
Below result as expected
id_a id_b fname
1 NULL NULL
2 NULL NULL
3 3 b3
4 NULL NULL
2. select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
where 1 = 1
and b.lname = 'B3'
Below result as expected (I understand the filter that happens in the resultset between the above 2)
id_a id_b fname
3 3 b3
3. This is really confusing for me. Please note there is no records for id_a "4" in the second table.
select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
where 1 = 1
and ( b.lname = 'B3' or b.id_b is null ) ---> Was thinking this will return all the records as in the first query. BUT it's neither of that.
id_a id_b fname
3 3 b3
4 NULL NULL
Can someone please help on this. Went through some of posts. However it discussed about the first two queries, which I understand well.
Not sure how this third query works. Thanks in advance.
January 9, 2019 at 4:50 am
The where in query 3 is applied to this query:select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
which returns these results:id_a id_b fname
1 1 b1
2 2 b2
3 3 b3
4 NULL NULL
This query will return what you were expecting:select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b and b.lname = 'B3'
where (b.lname = 'B3' or b.id_b is null )
January 9, 2019 at 5:15 am
You've created a set of all the records in table temp_1
left joined it to Temp_2 using the ID
You've then applied a where clause to this set that filters this set to the ones where the lname in table 2 is B3 or the id is NULL
The Where clause is applied to the resulting set so is removing records that do not match your where clause.
In your first query you are not applying a where clause you are filtering in the join.
This will return what you were expecting a bit like the first query.
select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
and ( b.lname = 'B3' or b.id_b is null )
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply