Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Handling null in sub queries

Tags:

sql

join

null

mysql

I have two tables with me. Say one has list of actions (the original table that I work on has a list of messages sent and received) and the other is a user table, with the full name of the users. I have replicated an issue of what I have right now.

The users found in the actions table (messages table) may not be present in the users table. I am using a subquery to select the full name of the users against the username in the original query for the actions table. When the user in the actions table is not present in the users table, MySQL returns a NULL and this is expected. But instead, I want it to return the original username.

Say, praveen is there both in the users table as well as actions table. So, I get something like this: Praveen Kumar likes something.. Say, I have another username, bluff that is not present in the users table, and the action becomes this way: NULL shares something.. Here instead of NULL, is it possible to return the username itself, like bluff shares something.?

My SQL Fiddle, schema details and other references have been provided here. Let me know if I can do it in a better way. Thanks.

MySQL 5.5.30 Schema Setup

CREATE TABLE `users`
    (`username` varchar(15), `fullname` varchar(15))
;

INSERT INTO `users`
    (`username`, `fullname`)
VALUES
    ('praveen', 'Praveen Kumar'),
    ('jeff', 'Jeff Atwood')
;

CREATE TABLE `actions`
    (`user` varchar(7), `action` varchar(7))
;

INSERT INTO `actions`
    (`user`, `action`)
VALUES
    ('praveen', 'Like'),
    ('jeff', 'Comment'),
    ('praveen', 'Answer'),
    ('praveen', 'Share'),
    ('jeff', 'Comment'),
    ('bluff', 'Share'),
    ('jeff', 'Like')
;

Query

SELECT *, (
  SELECT `fullname` FROM `users` WHERE `user` = `username`
) AS `name` FROM `actions`

Result

+---------+---------+---------------+
| USER    | ACTION  | NAME          |
+---------+---------+---------------+
| praveen |    Like | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
| praveen |  Answer | Praveen Kumar |
| praveen |   Share | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
|   bluff |   Share | (null)        |
|    jeff |    Like | Jeff Atwood   |
+---------+---------+---------------+

Expected Output

+---------+---------+---------------+
| USER    | ACTION  | NAME          |
+---------+---------+---------------+
| praveen |    Like | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
| praveen |  Answer | Praveen Kumar |
| praveen |   Share | Praveen Kumar |
|    jeff | Comment | Jeff Atwood   |
|   bluff |   Share | bluff         |
|    jeff |    Like | Jeff Atwood   |
+---------+---------+---------------+
like image 749
Praveen Kumar Purushothaman Avatar asked Oct 29 '25 05:10

Praveen Kumar Purushothaman


1 Answers

Use IFNULL

SELECT *, IFNULL((
  SELECT `fullname` FROM `users` WHERE `user` = `username`
),`user`) AS `name` FROM `actions`

SQLFiddle

EDIT:

Also COALESCE does the same

SELECT *, COALESCE((
  SELECT `fullname` FROM `users` WHERE `user` = `username`
),`user`) AS `name` FROM `actions`
like image 119
Muhammad Hani Avatar answered Oct 30 '25 21:10

Muhammad Hani



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!