CODEWITHSUNDEEP
mysqljoinnullrows
Charma
Charma

Reputation: 1107

MySQL JOIN rows if existing else fill with NULL

I have two tables of peoples names and properties. Some have no entries yet and I would like to create a full list of all people and their properties (if available) else have them listed with property "NULL". That's my simplicified example:

names

id     name
-------------
1      Jon
2      Peter
3      Sam
4      Bruce
...

properties

names_id    property
--------------------
1           foo
3           bar
...

desired result:

id     name     property
------------------------
1      Jon      foo
2      Peter    NULL
3      Sam      bar
4      Bruce    NULL
...

Thank you!

Upvotes: 5

Views: 9579

Answers (3)

Sunny Chawla
Sunny Chawla

Reputation: 381

You could try:

select A.id, A.name, B.property 
from names A
left join properties B
on A.id=B.names_id ;
WHERE  A.id IS NULL

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

Here, using join:

  select A.id, A.name, B.property
    from table1 A
    left outer join table2  B on A.id=B.names_id

Upvotes: 9

sam
sam

Reputation: 229

You can use left join in this case

select A.id,A.name,B.property
from names A
left join properties B
on A.id=B.names_id ;

Upvotes: 3

Related Questions