Postgresql - IN

使用 Postgresql IN 检索查询是否与列表中的任何值相匹配。

语法

value IN (value1, value2, ...)

如果匹配，则返回 TRUE，否则返回 FALSE。

value IN (SELECT column_name FROM table_name)

示例

SELECT customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id IN (1, 2)
ORDER BY
	return_date DESC;

以上代码用=和OR可以这样写

SELECT customer_id,
  rental_id,
  return_date
FROM
  rental
WHERE
  customer_id = 1 OR customer_id = 2
ORDER BY
  return_date DESC;

使用IN运算符的查询比使用=和OR运算符的查询更快，更具可读性。

NOT IN

查询返回的结果不包含列表中的任何值。

SELECT customer_id,
  rental_id,
  return_date
FROM
  rental
WHERE
  customer_id NOT IN (1, 2)

同样以上代码可以使用<>和AND运算符

SELECT customer_id
FROM rental
WHERE CAST (return_date AS DATE) = '2005-05-27'
ORDER BY customer_id;

SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer
WHERE
	customer_id IN (
		SELECT customer_id
		FROM rental
		WHERE CAST (return_date AS DATE) = '2005-05-27'
	)
ORDER BY customer_id;