CODEWITHSUNDEEP
mysqlsql
Ankur Rai
Ankur Rai

Reputation: 297

SQL query to return default value for a column if no Match in two different tables

I need to create an output table matching two tables. If the second table does not match with the first table then return a default value for a column.

Example-

Table 1 -

|---------------------|------------------|
|          Id         |        Name      |
|---------------------|------------------|
|          1          |        Jon       |
|---------------------|------------------|
|          2          |        Dan       |
|---------------------|------------------|
|          3          |        Mark      |
|---------------------|------------------|
|          4          |      Phillips    |
|---------------------|------------------|
|          5          |       Watson     |
|---------------------|------------------|

Table 2 -

|---------------------|------------------|
|          Name       |      Result      |
|---------------------|------------------|
|          Jon        |       Pass       |
|---------------------|------------------|
|        Phillips     |       Pass       |
|---------------------|------------------|
|         Watson      |       Fail       |
|---------------------|------------------|

Final Output Table with Join and if data does not match in second table then assign a default value "Fail" to it - 

|---------------------|------------------|------------------|
|          Id         |        Name      |      Result      |
|---------------------|------------------|------------------|
|          1          |        Jon       |       Pass       |
|---------------------|------------------|------------------|
|          2          |        Dan       |       Fail       |
|---------------------|------------------|------------------|
|          3          |        Mark      |       Fail       |
|---------------------|------------------|------------------|
|          4          |      Phillips    |       Pass       |
|---------------------|------------------|------------------|
|          5          |       Watson     |       Fail       |
|---------------------|------------------|------------------|

How can this be achieved in SQL.

Upvotes: 1

Views: 1347

Answers (1)

Steven
Steven

Reputation: 15283

Left join table1 with table2 and use coalesce to replace NULL values (when right table has no match) with the default value:

select t1.id, 
       t1.name, 
       coalesce(t2.result, 'fail') as Result      
from Table_1 as t1
left outer join Table_2 as t2 on t1.name = t2.name

Upvotes: 4

Related Questions