CODEWITHSUNDEEP
sqlsql-server-2008
Kevin M.
Kevin M.

Reputation: 139

Select Row from Other Table if Doesn't Exist in Current Table

I have two tables:

Table1

id   name       qty
1    Tedd       6
2    Jim        7
3    Sally      8
4    Victoria   1

Table2

id   name       qty
1    Tedd       2
2    Jim        2
3    Sally      2
4    Victoria   1
5    Alex       9

I need to select all the rows from Table1. However, if a row exists in Table2 that doesn't exist in Table1, I need to include that in the result set. So, in the end, my query should return this:

id   name       qty
1    Tedd       6
2    Jim        7
3    Sally      8
4    Victoria   1
5    Alex       9

Is there a way I can do this? Thanks.

Upvotes: 0

Views: 741

Answers (1)

Taryn
Taryn

Reputation: 247860

You can use a FULL OUTER JOIN:

select 
  coalesce(t1.id, t2.id) id,
  coalesce(t1.name, t2.name) name,
  coalesce(t1.qty, t2.id) qty
from table1 t1
full outer join table2 t2
  on t1.id = t2.id

See SQL Fiddle with Demo

Upvotes: 2

Related Questions