SQL logical operators
Logical operators combine or manipulate conditions in a SQL query.
Operator | Meaning | |
---|
AND | Returns true if both operands are true. Otherwise, returns false. | |
BETWEEN | Returns true if the left operand is within the range of the right operand. | |
EXISTS | Returns true if the results of a subquery are not empty. | |
IN | Returns true if the left operand is in the right operand list. | |
LIKE | Returns true if the left operand matches the right operand pattern string. | |
NOT | Negates the subsequent expression. | |
OR | Returns true if any operand is true. Otherwise, returns false. | |
Query examples on this page use the following sample data sets:
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"
AND
operator in the WHERE
clause
SELECT *
FROM home
WHERE
co > 10
AND room = 'Kitchen'
co | hum | room | temp | time |
---|
18 | 36.9 | Kitchen | 23.3 | 2025-05-10T18:00:00Z |
22 | 36.6 | Kitchen | 23.1 | 2025-05-10T19:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2025-05-10T20:00:00Z |
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"
BETWEEN
operator in the WHERE
clause
SELECT *
FROM home
WHERE
co BETWEEN 5 AND 10
co | hum | room | temp | time |
---|
7 | 36 | Kitchen | 22.4 | 2025-05-10T16:00:00Z |
9 | 36 | Kitchen | 22.7 | 2025-05-10T17:00:00Z |
5 | 35.9 | Living Room | 22.6 | 2025-05-10T17:00:00Z |
9 | 36.2 | Living Room | 22.8 | 2025-05-10T18:00:00Z |
The EXISTS
operator returns true
if result of a
correlated subquery
is not empty. Otherwise it returns false
.
See SQL subquery operators.
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
co | hum | room | temp | time |
---|
1 | 36.5 | Kitchen | 22.8 | 2025-05-10T13:00:00Z |
1 | 36.3 | Kitchen | 22.8 | 2025-05-10T14:00:00Z |
1 | 36.1 | Living Room | 22.3 | 2025-05-10T15:00:00Z |
4 | 36 | Living Room | 22.4 | 2025-05-10T16:00:00Z |
5 | 35.9 | Living Room | 22.6 | 2025-05-10T17:00:00Z |
18 | 36.9 | Kitchen | 23.3 | 2025-05-10T18:00:00Z |
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.
IN
operator with a list in the WHERE
clause
SELECT *
FROM home
WHERE
room IN ('Bathroom', 'Bedroom', 'Kitchen')
LIMIT 4
co | hum | room | temp | time |
---|
0 | 35.9 | Kitchen | 21 | 2025-05-10T08:00:00Z |
0 | 36.2 | Kitchen | 23 | 2025-05-10T09:00:00Z |
0 | 36.1 | Kitchen | 22.7 | 2025-05-10T10:00:00Z |
0 | 36 | Kitchen | 22.4 | 2025-05-10T11:00:00Z |
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
co | hum | room | temp | time |
---|
0 | 35.9 | Living Room | 21.1 | 2025-05-10T08:00:00Z |
0 | 35.9 | Kitchen | 21 | 2025-05-10T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2025-05-10T09:00:00Z |
0 | 36.2 | Kitchen | 23 | 2025-05-10T09:00:00Z |
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"
LIKE
operator in the WHERE
clause
SELECT *
FROM home
WHERE
room LIKE '%Room'
LIMIT 4
co | hum | room | temp | time |
---|
0 | 35.9 | Living Room | 21.1 | 2025-05-10T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2025-05-10T09:00:00Z |
0 | 36 | Living Room | 21.8 | 2025-05-10T10:00:00Z |
0 | 36 | Living Room | 22.2 | 2025-05-10T11:00:00Z |
The InfluxDB SQL implementation supports the following wildcard characters when
using the LIKE
operator to match strings to a pattern.
Character | Description |
---|
% | Represents zero or more characters |
_ | Represents any single character |
The NOT
operator negates the subsequent expression.
SELECT NOT true AS "NOT condition"
NOT IN
SELECT *
FROM home
WHERE
room NOT IN ('Kitchen', 'Bathroom')
LIMIT 4
co | hum | room | temp | time |
---|
0 | 35.9 | Living Room | 21.1 | 2025-05-10T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2025-05-10T09:00:00Z |
0 | 36 | Living Room | 21.8 | 2025-05-10T10:00:00Z |
0 | 36 | Living Room | 22.2 | 2025-05-10T11:00:00Z |
NOT EXISTS
SELECT *
FROM
home home_actions
WHERE NOT EXISTS (
SELECT *
FROM home
WHERE
home.co = home_actions.co + 4
)
ORDER BY time
co | hum | room | temp | time |
---|
7 | 36 | Kitchen | 22.4 | 2025-05-10T16:00:00Z |
4 | 36 | Living Room | 22.4 | 2025-05-10T16:00:00Z |
9 | 36 | Kitchen | 22.7 | 2025-05-10T17:00:00Z |
9 | 36.2 | Living Room | 22.8 | 2025-05-10T18:00:00Z |
17 | 36.4 | Living Room | 22.2 | 2025-05-10T20:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2025-05-10T20:00:00Z |
NOT BETWEEN
SELECT *
FROM home
WHERE
co NOT BETWEEN 1 AND 22
AND room = 'Kitchen'
co | hum | room | temp | time |
---|
0 | 35.9 | Kitchen | 21 | 2025-05-10T08:00:00Z |
0 | 36.2 | Kitchen | 23 | 2025-05-10T09:00:00Z |
0 | 36.1 | Kitchen | 22.7 | 2025-05-10T10:00:00Z |
0 | 36 | Kitchen | 22.4 | 2025-05-10T11:00:00Z |
0 | 36 | Kitchen | 22.5 | 2025-05-10T12:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2025-05-10T20:00:00Z |
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"
OR
in the WHERE
clause
SELECT *
FROM home
WHERE
co > 20
OR temp > 23
co | hum | room | temp | time |
---|
18 | 36.9 | Kitchen | 23.3 | 2025-05-10T18:00:00Z |
22 | 36.6 | Kitchen | 23.1 | 2025-05-10T19:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2025-05-10T20:00:00Z |
Was this page helpful?
Thank you for your feedback!
Thank you for being part of our community!
We welcome and encourage your feedback and bug reports for InfluxDB 3 Core and this documentation.
To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.