Skip to content

Instantly share code, notes, and snippets.

@anupam-io
Created February 15, 2021 07:32
Show Gist options
  • Save anupam-io/6a718b97c5b28945cd91baf493c10810 to your computer and use it in GitHub Desktop.
Save anupam-io/6a718b97c5b28945cd91baf493c10810 to your computer and use it in GitHub Desktop.
How to set custom values instead of NULL in outer joins?
drop database if exists db;
create database db;
use db;
drop table if exists a,b;
create table a(attr1 varchar(50), score int(10));
insert into a (attr1, score)values
("info1", 1), ("info2", 2);
create table b(attr1 varchar(50));
insert into b (attr1)values
("info1"), ("info2"), ("info3"), ("info4");
-- start
select b.attr1, CASE WHEN a.score IS NULL THEN 0 ELSE a.score END as score
from a right join b
on a.attr1 = b.attr1
;
-- end
@anupam-io
Copy link
Author

Output:

Records: 4  Duplicates: 0  Warnings: 0

+-------+-------+
| attr1 | score |
+-------+-------+
| info1 |     1 |
| info2 |     2 |
| info3 |     0 |
| info4 |     0 |
+-------+-------+
4 rows in set (0.00 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment