LEFT JOIN した際に、該当するレコードがなければ、NULLとしてSELECTされるような動作をしますが、
in() で指定したidのレコードが無い場合、指定した文字列を表示するようなことはできないのでしょうか。

MySQL
1/* こういうテーブルがあって */
2select * from table ;
3+---------+----------+
4| id      | job_id   |
5+---------+----------+
6| 3624202 |   205445 |
7| 3625705 |   284762 | /* ★ */
8| 3625726 |   288515 |
9| 3625717 |   315320 | /* ★ */
10| 3625729 |   439715 | /* ★ */
11| 3625714 |   562133 |
12+---------+----------+
13
14/* ★行のレコードのidを in() で指定すると下記のようになりますが、 */
15select * from tables where id in (3625705, 3625717, 3625729) ;
16+---------+----------+
17| id      | job_id   |
18+---------+----------+
19| 3625705 |   284762 | /* ★ */
20| 3625717 |   315320 | /* ★ */
21| 3625729 |   439715 | /* ★ */
22+---------+----------+
23
24/* inで指定したレコードが無い場合も、下記のように表示したい */
25select CASE
26    WHEN id IS NOT NULL THEN id
27    WHEN id IS NULL THEN 'notfound'
28END
29from tables where id in (3625705, 3625717, 3625729) ;
30+---------+----------+
31| id      | job_id   |
32+---------+----------+
33| notfoud |   205445 |
34| 3625705 |   284762 | /* ★ */
35| notfoud |   288515 |
36| 3625717 |   315320 | /* ★ */
37| 3625729 |   439715 | /* ★ */
38| notfoud |   562133 |
39+---------+----------+

↑の条件は解決できましたが、テーブル2つを参照する場合などはどうすればよいでしょうか。
テーブルが2つあり、
JOINするか副問い合わせするかして、
username in ('A', 'D', 'E', 'X', 'Y') という条件に絞った上で、
loginname を ('AA', 'DD', 'EE', 'notfound', 'notfound') と表示させたいのです。

MySQL
1/* login TABLE */
2+--------+---------+-----------+
3|    id  | user_id | loginname |
4+--------+---------+-----------+
5|    21  |      1  |      AA   |
6|    22  |      2  |      BB   | /* ★ */
7|    23  |      3  |      CC   |
8|    24  |      4  |      DD   | /* ★ */
9|    25  |      5  |      EE   | /* ★ */
10|    26  |      6  |      FF   |
11+--------+---------+--------+ 
12
13/* user TABLE */
14+---------+----------+
15|     id  | username |
16+---------+----------+
17|       1 |       A  |
18|       2 |       B  | /* ★ */
19|       3 |       C  |
20|       4 |       D  | /* ★ */
21|       5 |       E  | /* ★ */
22|       6 |       F  |
23+---------+----------+ 
24
25/* usernameが'X', 'Y'のレコードが該当しない */
26SELECT
27    u.loginname
28FROM
29    login AS l
30LEFT JOIN
31    user AS u ON u.id = l.user_id
32WHERE
33    username in ('A', 'D', 'E', 'X', 'Y') ;
34+-----------+
35| loginname |
36+-----------+
37|      AA   |
38|      DD   |
39|      EE   |
40+-----------+
41
42/* 下記のようなクエリで、'B','C','F'を出さず、'X','Y'も表示したい */
43/* 案1 */
44SELECT
45    CASE
46        WHEN u.loginname IS NULL THEN 'notfound'
47        ELSE u.loginname
48    END
49FROM
50    login AS l
51LEFT JOIN
52    user AS u ON u.id = l.user_id
53WHERE
54    username in ('A', 'D', 'E', 'X', 'Y') ;
55+-----------+
56| loginname |
57+-----------+
58|       AA  |
59|       DD  |
60|       EE  |
61| notfound  |
62| notfound  |
63+-----------+
64
65/* 案2 */
66SELECT
67    CASE
68        WHEN loginname IS NULL THEN 'notfound'
69        ELSE loginname
70    END
71FROM
72    login
73WHERE
74    user_id in (select id from user where username in ('A', 'D', 'E', 'X', 'Y')) ;

行動規範の内容に同意します

回答2件

惜しいところまで出来てますね。
もう一歩踏み込めば。

SQL
1select CASE WHEN id in (3625705, 3625717, 3625729)  
2         THEN cast(id as VARCHAR(255)) ELSE 'notfound'
3       END as id
4     , job_id
5from tables

投稿2018/10/10 15:41

sazi

総合スコア25195

teityura

2018/10/10 16:54

回答、ありがとうございます。 WHENの部分に in を使うことができたんですね。castもなるほどです。ただ、私が出したかった条件はもう少し複雑でしたので、追記させていただきました。

sazi

2018/10/11 05:17

username ( 'X', 'Y')はサンプルデータ上存在しませんが、それも行として出力するということですか？一時的に出力するものであればいいですけど、そうでないなら( 'X', 'Y')が存在するユーザーマスタなどと組み合わせる必要があります。

teityura

2018/10/11 15:48

存在しない行も出すということでした。一時的に出したいだけなので、テンポラリーテーブルで解決できました。回答、ありがとうございました。

行動規範の内容に同意します

ベストアンサー

こうです

SQL
1create table login_tbl (id int,user_id int,loginname varchar(10));
2insert into login_tbl values
3(21,1,'AA'),
4(22,2,'BB'),
5(23,3,'CC'),
6(24,4,'DD'),
7(25,5,'EE'),
8(26,6,'FF');
9
10create table user_tbl(id int,username varchar(10));
11insert into user_tbl values
12(1,'A'),
13(2,'B'),
14(3,'C'),
15(4,'D'),
16(5,'E'),
17(6,'F');
18
19select case user_id in (select id from user_tbl where username in ('A', 'D', 'E', 'X', 'Y'))
20when 1 then loginname else  'notfound' end as login_name
21from login_tbl;

調整版

上記データに対してこう

SQL
1create temporary table tmp_tbl(username varchar(10));
2insert into tmp_tbl values('A'),('D'),('E'),('X'),('Y');
3select coalesce(loginname,'notfound') as loginname  from tmp_tbl as t1
4left join user_tbl as t2 on t1.username=t2.username
5left join login_tbl as t3 on t2.id=t3.user_id
6;