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. | |
Sample data
Query examples on this page use the following sample data sets:
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
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 | 2022-01-01T18:00:00Z |
22 | 36.6 | Kitchen | 23.1 | 2022-01-01T19:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2022-01-01T20:00:00Z |
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
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 | 2022-01-01T16:00:00Z |
9 | 36 | Kitchen | 22.7 | 2022-01-01T17:00:00Z |
5 | 35.9 | Living Room | 22.6 | 2022-01-01T17:00:00Z |
9 | 36.2 | Living Room | 22.8 | 2022-01-01T18:00:00Z |
EXISTS
The EXISTS
operator returns true
if result of a
correlated subquery
is not empty. Otherwise it returns false
.
See SQL subquery operators.
Examples
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 | 2022-01-01T13:00:00Z |
1 | 36.3 | Kitchen | 22.8 | 2022-01-01T14:00:00Z |
1 | 36.1 | Living Room | 22.3 | 2022-01-01T15:00:00Z |
4 | 36 | Living Room | 22.4 | 2022-01-01T16:00:00Z |
5 | 35.9 | Living Room | 22.6 | 2022-01-01T17:00:00Z |
18 | 36.9 | Kitchen | 23.3 | 2022-01-01T18:00:00Z |
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
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 | 2022-01-01T08:00:00Z |
0 | 36.2 | Kitchen | 23 | 2022-01-01T09:00:00Z |
0 | 36.1 | Kitchen | 22.7 | 2022-01-01T10:00:00Z |
0 | 36 | Kitchen | 22.4 | 2022-01-01T11: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 | 2022-01-01T08:00:00Z |
0 | 35.9 | Kitchen | 21 | 2022-01-01T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2022-01-01T09:00:00Z |
0 | 36.2 | Kitchen | 23 | 2022-01-01T09:00:00Z |
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"
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 | 2022-01-01T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2022-01-01T09:00:00Z |
0 | 36 | Living Room | 21.8 | 2022-01-01T10:00:00Z |
0 | 36 | Living Room | 22.2 | 2022-01-01T11:00:00Z |
SQL wildcard characters
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 |
NOT
The NOT
operator negates the subsequent expression.
SELECT NOT true AS "NOT condition"
Examples
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 | 2022-01-01T08:00:00Z |
0 | 35.9 | Living Room | 21.4 | 2022-01-01T09:00:00Z |
0 | 36 | Living Room | 21.8 | 2022-01-01T10:00:00Z |
0 | 36 | Living Room | 22.2 | 2022-01-01T11: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 | 2022-01-01T16:00:00Z |
4 | 36 | Living Room | 22.4 | 2022-01-01T16:00:00Z |
9 | 36 | Kitchen | 22.7 | 2022-01-01T17:00:00Z |
9 | 36.2 | Living Room | 22.8 | 2022-01-01T18:00:00Z |
17 | 36.4 | Living Room | 22.2 | 2022-01-01T20:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2022-01-01T20: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 | 2022-01-01T08:00:00Z |
0 | 36.2 | Kitchen | 23 | 2022-01-01T09:00:00Z |
0 | 36.1 | Kitchen | 22.7 | 2022-01-01T10:00:00Z |
0 | 36 | Kitchen | 22.4 | 2022-01-01T11:00:00Z |
0 | 36 | Kitchen | 22.5 | 2022-01-01T12:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2022-01-01T20:00:00Z |
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
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 | 2022-01-01T18:00:00Z |
22 | 36.6 | Kitchen | 23.1 | 2022-01-01T19:00:00Z |
26 | 36.5 | Kitchen | 22.7 | 2022-01-01T20:00:00Z |
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community!
We welcome and encourage your feedback and bug reports for InfluxDB and this documentation.
To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.