# Druid SQL Operators

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.

Operators in Druid SQL typically operate on one or two values and return a result based on the values. Types of operators in Druid SQL include arithmetic, comparison, logical, and more, as described here.

When performing math operations, Druid uses 64-bit integer (long) data type unless there are double or float values. If an operation uses float or double values, then the result is a double, which is a 64-bit float. The precision of float and double values is defined by Java and the IEEE standard.

Keep the following guidelines in mind to help you manage precision issues:

- Long values can store up to 2^63 accurately with an additional bit used for the sign.
- Float values use 32 bits, and doubles use 64 bits. Both types are impacted by floating point precision. If you need exact decimal values, consider storing the number in a non-decimal format as a long value (up to the limit for longs). For example, if you need three decimal places, store the number multiplied by 1000 and then divide by 1000 when querying.

## Arithmetic operators

Operator | Description |
---|---|

`x + y` | Add |

`x - y` | Subtract |

`x * y` | Multiply |

`x / y` | Divide |

## Datetime arithmetic operators

For the datetime arithmetic operators, `interval_expr`

can include interval literals like `INTERVAL '2' HOUR`

.
This operator treats days as uniformly 86400 seconds long, and does not take into account daylight savings time.
To account for daylight savings time, use the `TIME_SHIFT`

function.
Also see `TIMESTAMPADD`

for datetime arithmetic.

Operator | Description |
---|---|

`timestamp_expr + interval_expr` | Add an amount of time to a timestamp. |

`timestamp_expr - interval_expr` | Subtract an amount of time from a timestamp. |

## Concatenation operator

Also see the CONCAT function.

Operator | Description |
---|---|

`x || y` | Concatenate strings `x` and `y` . |

## Comparison operators

Operator | Description |
---|---|

`x = y` | Equal to |

`x IS NOT DISTINCT FROM y` | Equal to, considering `NULL` as a value. Never returns `NULL` . |

`x <> y` | Not equal to |

`x IS DISTINCT FROM y` | Not equal to, considering `NULL` as a value. Never returns `NULL` . |

`x > y` | Greater than |

`x >= y` | Greater than or equal to |

`x < y` | Less than |

`x <= y` | Less than or equal to |

## Logical operators

Operator | Description |
---|---|

`x AND y` | Boolean AND |

`x OR y` | Boolean OR |

`NOT x` | Boolean NOT |

`x IS NULL` | True if x is NULL or empty string |

`x IS NOT NULL` | True if x is neither NULL nor empty string |

`x IS TRUE` | True if x is true |

`x IS NOT TRUE` | True if x is not true |

`x IS FALSE` | True if x is false |

`x IS NOT FALSE` | True if x is not false |

`x BETWEEN y AND z` | Equivalent to `x >= y AND x <= z` |

`x NOT BETWEEN y AND z` | Equivalent to `x < y OR x > z` |

`x LIKE pattern [ESCAPE esc]` | True if x matches a SQL LIKE pattern (with an optional escape) |

`x NOT LIKE pattern [ESCAPE esc]` | True if x does not match a SQL LIKE pattern (with an optional escape) |

`x IN (values)` | True if x is one of the listed values |

`x NOT IN (values)` | True if x is not one of the listed values |

`x IN (subquery)` | True if x is returned by the subquery. This will be translated into a join; see Query translation for details. |

`x NOT IN (subquery)` | True if x is not returned by the subquery. This will be translated into a join; see Query translation for details. |