How to get NULL if ON condition do not have matching data in mysql

Question

I have 2 tables in mysql. I want to JOIN them but even if no matching data from 2nd table, I need to show NULL but join them.

table1

id  |  name
-----------
1   |  name1
2   |  name2
3   |  name3

and second table

table2

id  |  service |  amount
------------------------
1   |  service1 |  10
2   |  service2 |  20
3   |  service1 |  20
4   |  service3 |  10

and the output I need is,

output

name  | amount
--------------
name1 | 10
name2 | NULL
name3 | 20

I have tried following query

SELECT t1.name,t2.amount 
FROM table1 t1  
LEFT JOIN table2 t2 ON t1.id = t2.id 
WHERE t2.service=1

But it will not return name2 since there is no matching id in 2nd table. How can I do that?

What do you mean, no matching id? The ids 1, 2 and 3 are also in second table. — GolezTrol
– GolezTrol, Commented Jan 20, 2015 at 11:55
Please see the WHERE condition. I have given service id 1. So no matching data in 2nd table — Joel James
– Joel James, Commented Jan 20, 2015 at 11:57
@Ajit : Where clause is required. I need only service1 data, but need others as NULL — Joel James
– Joel James, Commented Jan 20, 2015 at 12:01

juergen d · Accepted Answer · 2015-01-20 11:55:20Z

3

Your where clause turns your left join into an inner join*. Put the condition in the ON clause of your join

SELECT t1.name,t2.amount 
FROM table1 t1  
LEFT JOIN table2 t2 ON t1.id = t2.id 
                   AND t2.service = 1

_{* You filter in your where clause on data in your joined table. That forces the data to be filtered.}

answered Jan 20, 2015 at 11:55

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1