CODEWITHSUNDEEP
sql-serverjoin
Sachin25
Sachin25

Reputation: 17

SQL joins on Null values

Hi I have 2 tables A and B

Table A has 2 columns a1 (int) and a2(varchar(5)) Table B has 2 columns b1 (int) and b2(varchar(5))

Table A has values as 

a1     a2
1      aaa
NULL   bbb
2      ccc
3      ddd

Table B has Values as

b1     b2
1      app
2      new
3      disc

I want to get the result as

a1     a2      b2
1      aaa     app
NULL   bbb     NULL
2      ccc     new
3      ddd     disc

Note--

Bear in mind Im joining a1 with b1 but a1 has NULL and I want Null as well in my result.

Upvotes: 0

Views: 82

Answers (4)

Bilal Akil
Bilal Akil

Reputation: 4755

You can use something called a LEFT JOIN to achieve these results. I interpret it's functionality as:

Give me rows from the table on the left and if any rows from the table on the right fulfill my conditions, join them. Otherwise use NULL values in their place.

So if you say SELECT * FROM A LEFT JOIN B ON ..., A is on the left and will hence always be selected. B on the right will only be used if the ON condition is satisfied. If it isn't, then NULL will be used in place of it's values, giving you exactly what you wanted!

I'd just like to point out that things might get weird if B ever has a NULL value for b1. As such, I'd add a check like this:

SELECT a1, a2, b2
FROM A
LEFT JOIN B
  ON a1 IS NOT NULL AND a1 = b1;

You can see the fiddle running here: http://sqlfiddle.com/#!9/5ca6d/5

EDIT: That null thing doesn't seem to cause any problems in MySQL 5.6 since NULL = NULL is false (and so is NULL != NULL), but I'd feel weird leaving it unaccounted for..

Upvotes: 2

pala_
pala_

Reputation: 9010

You're just looking for a left join

select tablea.*, tableb.b2 from
  tablea left join tableb
    on tablea.a1 = tableb.b1

demo here

Upvotes: 0

smk
smk

Reputation: 5842

select A.a1,A.a2,B.b2 from A left join B on A.a1 = B.b1;

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

A left join should do what you want:

select a.a1, a.a2, b.b2
from a left join
     b
     on a.a1 = b.b1;

This is standard SQL so it will work in any database.

Upvotes: 0

Related Questions