case when遇到的问题

1、数据库表

user表结构和数据

CREATE TABLE `user`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `status` int(11) NOT NULL DEFAULT 0 COMMENT '状态',
  `points` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '积分',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES (1, '张三', 12, 0, 0, '2020-04-20 19:07:47', '2020-04-20 19:07:47');

user_test表

CREATE TABLE `user_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2、case when查询错误

当user_test没有数据时，也要查出user的数据

SELECT
    u.*,
    ut.* 
FROM
    USER u
    LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
WHERE
    u.id = 1 
AND
CASE
    WHEN u.age = 11 THEN
    ut.id = 1 END;

查询结果是空的，因为没有else。

3、正确的写法

SELECT
    u.*,
    ut.* 
FROM
    USER u
    LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
WHERE
    u.id = 1 
AND
CASE
    WHEN u.age = 11 THEN
    ut.id = 1 ELSE 1 = 1 END;

4、OR的写法

SELECT
    u.*,
    ut.* 
FROM
    USER u
    LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
WHERE
    u.id = 1 
    AND ( ( u.age = 11 AND ut.id = 1 ) OR ( 1 = 1 ) );