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;