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