In a recent project I needed to come up with a way to return information from one of two tables, based on whether or not the value of the first column was null.
In situations like this, the SELECT CASE statement is the best option.
This is the syntax:
( SELECT CASE WHEN [condition] THEN [return if true] ELSE [return if false] END )
Its important to know that you can use SELECT CASE in both the selects AND the WHERE CLAUSES of a sql statement.
Here is a full query, where, if the person’s address is null in table a, it will provide the address from table b.
You would use the same syntax in the WHERE CLAUSE.
SELECT a.name as Name, ( SELECT CASE WHEN a.address IS NULL THEN b.address ELSE a.address END ) as Address FROM users AS a INNER JOIN userdata AS b ON a.UID = b.UID