Logical

Logical operators combine or manipulate conditions in a SQL query.

[!Note]

Sample data

Query examples on this page use the following sample data sets:

• Home sensor sample data
• Home sensor actions sample data

AND {.monospace}

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.

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

SELECT true AND false AS "AND condition"

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

Examples

{{< expand-wrapper >}} {{% expand "AND operator in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

BETWEEN {.monospace}

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

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

SELECT 6 BETWEEN 5 AND 8 AS "BETWEEN condition"

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

Examples

{{< expand-wrapper >}} {{% expand "BETWEEN operator in the WHERE clause" %}}

SELECT *
FROM home
WHERE
  co BETWEEN 5 AND 10

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

EXISTS {.monospace}

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

See SQL subquery operators.

Examples

{{< expand-wrapper >}} {{% expand "EXISTS operator with a subquery in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

IN {.monospace}

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

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

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

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

See SQL subquery operators.

Examples

{{< expand-wrapper >}} {{% expand "IN operator with a list in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{% expand "IN operator with a subquery in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

LIKE {.monospace}

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

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

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

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

{{< expand-wrapper >}} {{% expand "LIKE operator in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

SQL wildcard characters

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

NOT {.monospace}

The NOT operator negates the subsequent expression.

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

SELECT NOT true AS "NOT condition"

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

Examples

{{< expand-wrapper >}} {{% expand "NOT IN" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "NOT EXISTS" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "NOT BETWEEN" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}

OR {.monospace}

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.

{{< flex >}} {{% flex-content "two-thirds operator-example" %}}

SELECT true OR false AS "OR condition"

{{% /flex-content %}} {{% flex-content "third operator-example" %}}

{{% /flex-content %}} {{< /flex >}}

Examples

{{< expand-wrapper >}} {{% expand "OR in the WHERE clause" %}}

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

{{% influxdb/custom-timestamps %}}

{{% /influxdb/custom-timestamps %}} {{% /expand %}} {{< /expand-wrapper >}}