MySQLでのSELECTで名前変更したものをを検索する方法。

Question

前提・実現したいこと

SQLでSELECTで名前を変更した値を検索したい。

該当のソースコード

SELECT
  CASE
  WHEN gender = '女' THEN 0
  WHEN gender = '男' THEN 1
  WHEN gender = null THEN 2
  ELSE 99
  END as gender1
  FROM users where gender1 = 2

SELECT
  IF(
      gender = null, gender = 2,
      gender
) gender1
FROM users
where gender1 = 2;


create table users
(
  id     int auto_increment
    primary key,
  gender varchar(10) null
);


INSERT INTO users (gender) VALUES ('男');
INSERT INTO users (gender) VALUES ('男');
INSERT INTO users (gender) VALUES ('男');
INSERT INTO users (gender) VALUES ('男');
INSERT INTO users (gender) VALUES ('男');
INSERT INTO users (gender) VALUES ('女');
INSERT INTO users (gender) VALUES ('女');
INSERT INTO users (gender) VALUES ('女');
INSERT INTO users (gender) VALUES ('女');
INSERT INTO users (gender) VALUES ('女');
INSERT INTO users (gender) VALUES (null);
INSERT INTO users (gender) VALUES (null);
INSERT INTO users (gender) VALUES (null);
INSERT INTO users (gender) VALUES (null);
INSERT INTO users (gender) VALUES (null);

期待値

id	gender
11	null
12	null
13	null
14	null
15	null

ご教授ください。

追記

SELECT
  gender as g1
FROM users
where g1 is null;

Comments

[退会済みユーザー]

DB は SQL Server, MySQL, Oracle, その他何でしょうか？　SQL には DB による方言のようなものがあって DB によって異なる場合がありますので書くようにしてください。

あと、期待する結果はどういうもので、現状どうなっているのかも書いてください。

[larara]

申し訳ありません、MySQLになります。

[kaina]

usersテーブルのgenderに入っている値は男 or 女 or nullのだけど
where条件ではgender = 2とか値で検索したいってこと？

[larara]

はいそう言うことです。

Answer 1

gender を編集して変換した物との条件にしたいなら、SQLをネストさせます。

select * from (
  SELECT *
        , CASE 
          WHEN gender = '女' THEN 0
          WHEN gender = '男' THEN 1
          WHEN gender = null THEN 2
          ELSE 99
          END as gender1
  FROM users 
) t1
where gender1 = 2

もう一つは入力の条件を変換する事です。
こちらの方が比較対象を変換しない分高速になる可能性があります。

set @gender=2;
select * from user
where gender = case @gender when 0 then '女' when 1 then '男' end
   0r (gender is null and @gender=2)
   0r @gender=99
;

Answer 2

テーブルに格納されている値はあくまでNULLです。
CASE THENは出力する値を指定しているのみであり、
テーブル内の値を変更しているわけではありません。

どうしてもNULLではなく、2で検索したいのであれば、テーブル内に別カラムを追加し
値を格納するようにしてください。

Answer 3

SQLには実行順序があります。
上から順番に実行されます
0. FROM
0. JOIN
0. WHERE
0. GROUP BY
0. HAVING
0. SELECT
0. DISTINCT
0. ORDER BY
0. LIMIT
なので
「usersテーブルのgenderに入っている値は男 or 女 or nullだけど
where条件ではgender = 2とか値で検索したい」というのは難しいのではないでしょうか。

Answer 4

想像ですが、WHERE 句で gender = NULL とかしても期待する結果が得られないから困っているような気がします。

そうであれば、WHERE gender IS NULL とすれば解決するのではないでしょうか？

MySQLで値がNULLかどうかを判定する（is null や is not null の使い方）
https://26gram.com/mysql-is-null

想像が違ったらすみません。その場合はもう少し詳しく何がしたいのか書いてください。

【追記】

質問の追記のコード、

SELECT
  gender as g1
FROM users
where g1 is null;

は Unknown column 'g1' in 'where clause' というエラーで動かないと思いますが、それが動くようにできればいいということなら、以下のようにしてはいかがですか？

Comments

[larara]

あくまでも上のnullは参考値であり、本来やりたいことはSELECTで変更した値を検索したいです。

[退会済みユーザー]

gender = NULL は意味がないということは理解されてますか？

質問のソースコードで gender = null がいくつか出てきますが、それを gender IS NULL に書き換えたらどうなりますか？

[larara]

タイトルか変更いたしました。
本来やりたいことはASした値を取得する方法になります。

[退会済みユーザー]

上のコメントに答えてください。そうしてもらわないと話がかみ合わなくなります。

Answer 5

sqlにおいてnullは特別な値なので等号では合致しません
カラム is nullで検索してください

Comments

[larara]

あくまでも上のnullは参考値であり、本来やりたいことはSELECTで変更した値を検索したいです。

[yambejp]

とりあえず

SELECT *
FROM users WHERE CASE
WHEN gender = '女' THEN 0
WHEN gender = '男' THEN 1
WHEN gender is null THEN 2
ELSE 99
END = 2

[yambejp]

わざわざ変換してから付け合せをして、
表示するのは元データっていうのはいかにも非効率な感じが否めません

[yambejp]

ちなみにお勧めはしないですが、mysqlはgroup byしなくても
havingできてしまう仕様があります

SELECT *,CASE
WHEN gender = '女' THEN 0
WHEN gender = '男' THEN 1
WHEN gender is null THEN 2
ELSE 99
END as gender_num
FROM users
HAVING gender_num= 2