CODEWITHSUNDEEP
mysqlsql
user461316
user461316

Reputation: 913

WHERE statement with condition when one column is NULL

Let's say I have

ID       |   Column 1 | Column 2
  2      |      NULL  |     "a"
  3      |      "b"   |    NULL
  4      |       "c"  |     "c"

What I want to write is this:

 SELECT ID FROM table, AnotherTable 
  WHERE (Table.Column1 = AnotherTable.Column IF Table.Column1 IS NOT NULL 
     OR Table.Column2 = AnotherTable.Column IF Table.Column2 IS NOT NULL)

EDIT: I am also adding the case IF Table.Column1 IS NOT NULL AND Table.Column2 IS NOT NULL THEN CHOOSE Table.Column2 = AnotherTable.Column

Upvotes: 5

Views: 147

Answers (1)

eggyal
eggyal

Reputation: 126025

You can use the COALESCE() function:

SELECT ID
FROM   table JOIN AnotherTable
               ON AnotherTable.Column = COALESCE(table.Column1, table.Column2)

Upvotes: 10

Related Questions