CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6707

How can I replace null?

I have two tables like these:

// users
+----+--------+------------+----------------+------------+
| id |  name  | reputation |     avatar     | visibility |
+----+--------+------------+----------------+------------+
| 1  | John   | 344        | er0942334r4... | 1          |
| 2  | Peter  | 65544      | 340ojfc343f... | 0          |
+----+--------+------------+----------------+------------+

// posts
+----+--------+------------+-----------+
| id | title  |  content   | author_id |
+----+--------+------------+-----------+
| 1  | title1 | content1   | 2         |
| 2  | title2 | content2   | 1         |
+----+--------+------------+-----------+

Also here is my query:

SELECT p.*, u.name, u.reputation rep, u.avatar
FROM posts p
LEFT JOIN users u
ON u.id = p.author_id AND u.visibility = 1
// please focus on this-> ^^^^^^^^^^^^^^^^

And here is the current result:

+----+--------+------------+-----------+--------+------+----------------+
| id | title  |  content   | author_id |  name  | rep  |     avatar     |
+----+--------+------------+-----------+--------+------+----------------+
| 1  | title1 | content1   | 2         |        |      |                |
| 2  | title2 | content2   | 1         | John   | 344  | er0942334r4... |
+----+--------+------------+-----------+--------+------+----------------+

Now I want to set some default values when visibility <> 1 or name or rep or avatar is null. So here is the expected result:

+----+--------+------------+-----------+--------+------+----------------+
| id | title  |  content   | author_id |  name  | rep  |     avatar     |
+----+--------+------------+-----------+--------+------+----------------+
| 1  | title1 | content1   | 2         | NoName | 1    | default        |
| 2  | title2 | content2   | 1         | John   | 344  | er0942334r4... |
+----+--------+------------+-----------+--------+------+----------------+

How can I do that?

Note: I can do that by PHP like this:

$name = is_null($result['name'])   ? 'NoName'  : $result['name'];
$name = is_null($result['rep'])    ? 1         : $result['rep'];
$name = is_null($result['avatar']) ? 'default' : $result['avatar'];

But I want to know how can I do that by pure sql.

Upvotes: 1

Views: 63

Answers (1)

sagi
sagi

Reputation: 40491

You can simply use COALESCE() :

SELECT p.*,
       COALESCE(u.name,'NoName') as `name`,
       COALESCE(u.reputation,1) as rep,
       COALESCE(u.avatar,'default') as avatar
FROM posts p
LEFT JOIN users u
ON u.id = p.author_id AND u.visibility = 1

You can also replace it with IFNULL . The difference between them is that COALESCE() can handle multiple arguments.

Upvotes: 1

Related Questions