Search code examples
sqlcaseinner-join

SQL inner join with conditional selection

I am new in SQL. Lets say I have 2 tables one is table_A and the other one is table_B. And I want to create a view with two of them which is view_1.

table_A:

id foo
1 d
2 e
null f

table_B

id name
1 a
2 b
3 c

and when I use this query :

SELECT DISTINCT table_A.id, table_B.name
FROM table_A 
INNER JOIN table_B ON table_B.id = table_A.id

the null value in table_A can't be seen in the view_1 since it is not found in table_B. I want view_1 to show also this null row like :

id name
1 a
2 b
null no entry

Should I create a 4. table? I couldn't find a way.

Solution

  • Try this Query:

    SELECT DISTINCT a.id,(CASE When b.name IS NULL OR b.name = '' Then 'No Entry' else b.name end) name FROM table_A a  
LEFT JOIN table_B b on a.id = b.id

    Try This