Oracle JOIN ON colA = colB: how to include null rows from colA

Question

I have two tables.

TABLE_A

| SURNAME | COL_X |

TABLE_B

| ID | COL_Y |

COL_X can be mapped towards the ID column in table B, and I need the values from COL_Y.

The below query works fine except when COL_X in TABLE_A has a NULL value. I would like to include those rows. How do I do that?

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
INNER JOIN TABLE_B b
ON a.COL_X = b.ID

I have tried the following query but it returns duplicate rows and can therefore not be used.

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
INNER JOIN TABLE_B b
ON a.COL_X = b.ID or a.COL_X IS NULL

Just use `left join` instead of `inner join`. Read this http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins/21085546#21085546 — Florin Ghita, Nov 25 '15 at 12:17

score 1 · Answer 1 · answered Nov 25 '15 at 12:14

1

Just use a LEFT JOIN instead of an INNER JOIN:

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
LEFT JOIN TABLE_B b
  ON a.COL_X = b.ID

answered Nov 25 '15 at 12:14

Frank Schmitt

score 1 · Accepted Answer · edited Nov 25 '15 at 12:21

1

You have tried using an inner join which displays all rows from both tables. You can probably use the left join to do what you expect:

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
LEFT JOIN TABLE_B b
  ON a.COL_X = b.ID

edited Nov 25 '15 at 12:21

Frank Schmitt

answered Nov 25 '15 at 12:16

Tabish Khan

You are right about this. Your solution works for the problem I have presented. However there is another variable which I should have shown from the start. The ON clause should exclude other rows in TABLE_A, like this: ON a.COL_Z = 'Z' and (here's what I showed) a.COL_X = b.ID. A left join resolves the issue with the second section, but also seems to ignore that COL_Z needs to equal 'Z'. Do you have any idea about that? Sorry for the confusion. You resolved the presented issue anyway. – user3341082 Nov 25 '15 at 12:40

score 1 · Answer 3 · answered Nov 25 '15 at 12:17

1

You should try an outer join, for example -

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
RIGHT OUTER JOIN TABLE_B b
ON a.COL_X = b.ID or a.COL_X IS NULL

answered Nov 25 '15 at 12:17

Yaron Idan

score 1 · Answer 4 · edited May 23 '17 at 12:15

1

Those rows will have no corespondent in tableB. The query is simple:

SELECT a.SURNAME, b.COL_Y
FROM TABLE_A a
LEFT JOIN TABLE_B b
ON a.COL_X = b.ID

edited May 23 '17 at 12:15

Community

answered Nov 25 '15 at 12:18

Florin Ghita

4 Answers4