Search code examples
sqloracle-databasecoalesce

COALESCE and NULL

I want to create an SQL query (using Oracle) where I am combining the values across 2 columns, each of which exist in different tables (they are joined by a CODE). Here is the query I have so far:

SELECT COALESCE(a.SERIES1,b.SERIES2)
FROM TABLE1 a, TABLE2 b
WHERE a.CODE = b.CODE

However, when I join columns together, I also want to return null as well. I think I am only getting the instances where SERIES1 = SERIES2...

Ideally, I want to get the following:

  • If SERIES1 is not null, return SERIES1
  • If SERIES2 is not null, return SERIES2
  • If SERIES1 and SERIES2 are both null, return null

I hope I am making this clear and that I'm using the correct terminology...

Thanks!

Solution

  • I think the problem is not with the COALESCE, but rather with the way that you did a join. It appears that you may be looking for an outer join, while you did an inner join.

    Although Oracle has its own syntax for doing an outer join the way you did (i.e. by listing two tables and placing the join condition in the WHERE clause) the ANSI syntax should be preferred:

    SELECT COALESCE(a.SERIES1,b.SERIES2)
FROM TABLE1 a
FULL OUTER JOIN TABLE2 b ON a.CODE = b.CODE