CODEWITHSUNDEEP
sqlsql-server-2005join
abc
abc

Reputation: 2437

join if entry doesn't exists in second table

I have two tables which are selected an joined,

Table1

PK    Val1    Val2     FK
1     a       b        10
2     c       d        11
3     e       f
4     g       h        12

Table2

PK    Val3
10     X
11     Y
12     Z

When I do a select on this both tables with an inner join and all the Val-Columns I get this result:

Result
PK    Val1    Val2     Val3
1     a       b        X
2     c       d        Y
4     g       h        Z

As you can see the third entry is missing. What I want is something like this:

Result
PK    Val1    Val2     Val3
1     a       b        X
2     c       d        Y
3     e       f
4     g       h        Z

How do I have to modify the joinquery

SELECT ... FROM Table1 INNER JOIN Table2 On Table1.FK = Table2.PK

Thank you, Karl

Upvotes: 9

Views: 6805

Answers (3)

ARUNRAJ
ARUNRAJ

Reputation: 489

SELECT ... FROM Table1 CROSS JOIN Table2

Upvotes: -1

John Woo
John Woo

Reputation: 263943

use LEFT JOIN instead of INNER JOIN

SELECT ... FROM Table1 LEFT JOIN Table2 On Table1.FK = Table2.PK

basically, INNER JOIN returns record where there is atleast a match on the other table. While LEFT JOIN returns all records on the table specified on the left side whether it has a match or none.

btw, LEFT OUTER JOIN is identical to LEFT JOIN

SQLFiddle Demo

Upvotes: 14

SRIRAM
SRIRAM

Reputation: 1888

use left outer join

SELECT ... FROM Table1 LEFT OUTER JOIN Table2 On Table1.FK = Table2.PK

Upvotes: 2

Related Questions