How to fetch NULL values in Mysql through joins?

Question

I want to fetch NULL values in Mysql. My query is like this-:

Select sc.message 
FROM shipping_comment sc,
sales_flat_order sfo
where sfo.shipping_comment_id = sc.shipping_comment_id
AND sfo.increment_id = 100000429

so If for the order no. 100000429 if customer given comment then it is showing correctly like below-:

   message
   sample comment ok

but say for any order, customer didn't give any comment. Say for another id 100000430. Then in sfo.shipping_comment_id value is stored in database as NULL. So how to fetch NULL for sc.message so that my output will be for order no. 100000430-:

message
NULL

One more problem is that shipping_comment (sc) only stores comments for which there is comment for order & for those only generate shipping_comment_id. So if there is no comment for order, there is no existence of any data in shipping comment table for that order. & for table sales_flat_order(sfo) if there is no comment for order, it store value is shipping_comment_id=NULL. So in that case where condition become-:

NULL=sc.shipping_comment_id

which is not taken by sc.message.

So how can I fetch NULL values in mysql table??? plz guide me.

Answer 1

I'm not sure what is the problem with your query? For the given order no. 100000429, your query will return the message if there is one in "shipping_comment"; otherwise it will return an empty result set.

Anyways, if you want to retrieve all order numbers and their comments, if any, this query should help:

SELECT `sfo`.*, `sc`.`message`
FROM `sales_flat_order` `sfo`
LEFT JOIN `shipping_comment` `sc` ON `sfo`.`shipping_comment_id` = `sc`.`shipping_comment_id`;

It will need a LEFT JOIN to return all orders.

Hope it helps!

Answer 2

You need a "left outer join". See this question for detail regarding syntax and semantics.

What is the difference between "INNER JOIN" and "OUTER JOIN"?