Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Determine if column value is null/non-null in MySQL

Tags:

sql

join

mysql

I have two tables, one containing user data, user, and a second that contains those users that have tied their accounts in with Twitter, user_twitter. As the second table does not contain a correspodning value for each user (those that have yet to connect their account with Twitter, for instance), I am querying using a LEFT JOIN:

SELECT DISTINCT `user_twitter`.uid AS `connected` 
FROM `user` 
LEFT JOIN (SELECT uid FROM `user_twitter`) AS `user_twitter` ON `user`.uid = `user_twitter`.uid

What I'd like to do is return a boolean value (true if user_twitter.uid is a non-null value, false if it is null). Any pointers?

like image 893
Unpossible Avatar asked Apr 05 '13 07:04

Unpossible


1 Answers

SELECT  a.uid AS `connected`,
        (b.uid IS NOT NULL) bool_val
FROM    `user` a
        LEFT JOIN `user_twitter` b
            ON a.uid = b.uid

will return 0 for false and 1 for true. But if you want string value for true or false,

SELECT  a.uid AS `connected`,
        CASE WHEN b.uid IS NOT NULL THEN 'True' ELSE 'False' END bool_val
FROM    `user` a
        LEFT JOIN `user_twitter` b
            ON a.uid = b.uid
like image 78
John Woo Avatar answered Sep 27 '22 19:09

John Woo