SQL logical operators

Logical operators combine or manipulate conditions in a SQL query.

AND

The AND operand returns true if both operands are true. Otherwise, it returns false. This operator is typically used in the WHERE clause to combine multiple conditions.

SELECT true AND false AS "AND condition"

Examples

SELECT *
FROM home
WHERE
  co > 10
  AND room = 'Kitchen'

BETWEEN

The BETWEEN operator returns true if the left numeric operand is within the range specified in the right operand. Otherwise, it returns false

SELECT 6 BETWEEN 5 AND 8 AS "BETWEEN condition"

Examples

SELECT *
FROM home
WHERE
  co BETWEEN 5 AND 10

EXISTS

The EXISTS operator returns true if result of a correlated subquery is not empty. Otherwise it returns false.

See SQL subquery operators.

Examples

SELECT *
FROM
  home home_actions
WHERE EXISTS (
  SELECT *
  FROM home
  WHERE
    home.co = home_actions.co - 1
)
ORDER BY time

IN

The IN operator returns true if the left operand is in the right operand list or subquery result. Otherwise, it returns false.

SELECT 'John' IN ('Jane', 'John') AS "IN condition"

See SQL subquery operators.

Examples

SELECT *
FROM home
WHERE
  room IN ('Bathroom', 'Bedroom', 'Kitchen')
LIMIT 4

SELECT *
FROM home
WHERE
  room IN (
    SELECT DISTINCT room
    FROM home_actions
  )
ORDER BY time
LIMIT 4

LIKE

The LIKE operator returns true if the left operand matches the string pattern specified in the right operand. LIKE expressions support SQL wildcard characters.

SELECT 'John' LIKE 'J_%n' AS "LIKE condition"

SELECT *
FROM home
WHERE
  room LIKE '%Room'
LIMIT 4

SQL wildcard characters

The InfluxDB SQL implementation supports the following wildcard characters when using the LIKE operator to match strings to a pattern.

NOT

The NOT operator negates the subsequent expression.

SELECT NOT true AS "NOT condition"

Examples

SELECT *
FROM home
WHERE
  room NOT IN ('Kitchen', 'Bathroom')
LIMIT 4

SELECT *
FROM
  home home_actions
WHERE NOT EXISTS (
  SELECT *
  FROM home
  WHERE
    home.co = home_actions.co + 4
)
ORDER BY time

SELECT *
FROM home
WHERE
  co NOT BETWEEN 1 AND 22
  AND room = 'Kitchen'

OR

The OR operator returns true if any operand is true. Otherwise, it returns false. This operator is typically used in the WHERE clause to combine multiple conditions.

SELECT true OR false AS "OR condition"

Examples

SELECT *
FROM home
WHERE
  co > 20
  OR temp > 23