使用 WHERE IN 返回 NULL [英] Returning NULL using WHERE IN

问题描述

我正在使用这个查询:

SELECT GROUP_CONCAT(column1) as values FROM archive WHERE column2 IN (21, 22)

查询从 Column2 具有指定值之一的行中返回 Column1 的值.

The query returns the values ​​of Column1 from lines whose Column2 has one of the specified values​​.

问题是在这种情况下，在表中找不到值 21.所以我需要返回一个NULL.但查询仅返回找到的值.

The problem is that in this case the value 21 is not found in the table. So I need to return a NULL. But the query is only returning the value found.

查询返回:

File 1

我希望:

File 1, NULL

推荐答案

A 右外连接、左外连接、case 以及not exists 也可以使用.下面的一些示例...(带有指向 SQL Fiddle 工作示例的链接).

A right outer join, left outer join, case and also not exists can also be utilized. Some examples below... (with links to SQL Fiddle working examples).

SELECT GROUP_CONCAT(CASE WHEN COLUMN1 IS NULL THEN "NULL" ELSE COLUMN1 END)
FROM archive
RIGHT OUTER JOIN
  (SELECT 21 AS id
   UNION SELECT 22 AS id) AS tmp ON tmp.id=archive.column2;

SQL 小提琴:http://sqlfiddle.com/#!2/805e1c/9

SELECT GROUP_CONCAT(
  CASE WHEN COLUMN1 IS NULL THEN "NULL" ELSE COLUMN1 END)
FROM archive
RIGHT OUTER JOIN temporary tmp ON tmp.id=archive.column2;

SQLFiddle:http://sqlfiddle.com/#!2/36fba/2

这篇关于使用 WHERE IN 返回 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！