Operators (detailed)

Navigation: Query Structure · Operator Precedence · Keywords

This file provides a per-operator description and concrete SQL examples for each operator supported by the engine.

Math Operators
Comparison Operators
Logical Operators
Cast Operators

Math Operators

Operator: `+`

Description:
Arithmetic addition.

Syntax:

expr1 + expr2

Inputs:

expr1, expr2 - Numeric expressions (INT, DOUBLE, DECIMAL, etc.), or a DATE, TIMESTAMP or DATETIME and a number of days (see Date arithmetic)

Output:

Numeric type (result type depends on operand types); a date or a timestamp for date arithmetic

Examples:

Basic Addition:

-- Add two numbers
SELECT 5 + 3 AS result;
-- Result: 8

-- Add column values
SELECT salary + bonus AS total_comp FROM emp;
-- Result example: if salary=50000 and bonus=10000 -> total_comp = 60000

-- Multiple additions
SELECT base_price + tax + shipping AS total_cost
FROM orders;

With Different Types:

-- Integer addition
SELECT 10 + 20 AS total;
-- Result: 30

-- Float addition
SELECT 10.5 + 20.3 AS total;
-- Result: 30.8

-- Mixed types (INT + DOUBLE)
SELECT 10 + 20.5 AS total;
-- Result: 30.5 (promoted to DOUBLE)

-- A number and a string column (n INT, s KEYWORD) are a type error, refused by name once the
-- column types are known
SELECT n + s AS x FROM t;
-- Error: Type mismatch: output 'INT' is not compatible with input 'KEYWORD'

-- To join them as text, convert the number
SELECT CONCAT(CAST(n AS VARCHAR), s) AS x FROM t;
-- Result: '3a' for n = 3, s = 'a'

NULL Handling:

-- NULL propagation
SELECT salary + NULL AS result FROM emp;
-- Result: NULL

-- Use COALESCE for default
SELECT salary + COALESCE(bonus, 0) AS total FROM emp;

Operator: `-`

Description:
Arithmetic subtraction or unary negation when used with single operand.

Syntax:

-- Binary (subtraction)
expr1 - expr2

-- Unary (negation)
-expr

Inputs:

expr1, expr2 - Numeric expressions
expr - Numeric expression (for unary)

Output:

Numeric type

Examples:

Subtraction:

-- Basic subtraction
SELECT 10 - 3 AS result;
-- Result: 7

-- Column subtraction
SELECT salary - tax AS net FROM emp;

-- Multiple subtractions
SELECT revenue - cost - overhead AS profit
FROM financials;

Unary Negation:

-- Negate a value
SELECT -balance AS negative_balance FROM accounts;

-- Negate column
SELECT -price AS negated_price FROM products;

-- In expressions
SELECT 100 + (-50) AS result;
-- Result: 50

Date Arithmetic (see Date arithmetic):

-- Date subtraction: the days between the two dates (BIGINT)
SELECT ship_date - order_date AS days_to_ship
FROM orders;

-- A date moved back by days (DATE)
SELECT order_date - 7 AS week_before
FROM orders;

-- With INTERVAL
SELECT order_date - INTERVAL 7 DAY AS week_ago
FROM orders;

Operator: `*`

Description:
Multiplication.

Syntax:

expr1 * expr2

Inputs:

expr1, expr2 - Numeric expressions

Output:

Numeric type

Examples:

Basic Multiplication:

-- Multiply two numbers
SELECT 5 * 3 AS result;
-- Result: 15

-- Calculate revenue
SELECT quantity * price AS revenue FROM sales;

-- Multiple multiplications (`length` is also a function name: quote it)
SELECT `length` * width * height AS volume
FROM boxes;

With Different Types:

-- Integer multiplication
SELECT 10 * 5 AS product;
-- Result: 50

-- Float multiplication
SELECT 10.5 * 2.0 AS product;
-- Result: 21.0

-- Mixed types
SELECT 10 * 2.5 AS product;
-- Result: 25.0

Practical Examples:

-- Calculate total with tax
SELECT price * (1 + tax_rate) AS total_price
FROM products;

-- Calculate discount
SELECT price * (1 - discount_percent / 100) AS discounted_price
FROM products;

-- Area calculation
SELECT width * height AS area FROM rectangles;

Operator: `/`

Description:
Division. / always yields a floating-point (DOUBLE) result, whatever its operands are — the result type is a property of the operator, not of its arguments. A zero divisor yields NULL; no guard of your own is required.

Syntax:

expr1 / expr2

Inputs:

expr1, expr2 - Numeric expressions

Output:

DOUBLE, always (NULL if the divisor is zero)

Examples:

Basic Division:

-- Divide two numbers
SELECT 10 / 2 AS result;
-- Result: 5.0

-- Per-unit price
SELECT total_price / quantity AS unit_price
FROM order_items;

-- Average, with no guard of your own: a zero divisor is NULL
SELECT total / order_count AS ratio FROM statistics;

Division is always decimal:

-- two integers still divide as decimals
SELECT 10 / 3 AS result;
-- Result: 3.3333333333333335

-- ... including two INTEGER columns
SELECT n / m AS result FROM t;   -- n = 7, m = 2  ->  3.5

-- a decimal operand changes nothing, and neither does a cast
SELECT 10.0 / 3 AS result;                 -- 3.333...
SELECT CAST(10 AS DOUBLE) / 3 AS result;   -- 3.333...

/ decides its own result type — an engine decision, since 0.24.0.

A division of numbers is floating-point whatever its operands are. Two INTEGER columns divide as decimals, and so do two integer literals:
-- n INTEGER = 7, m INTEGER = 2, price DOUBLE = 5.0
SELECT n / m AS c FROM t;                        -- 3.5   (was 3 before 0.24.0)
SELECT 10 / 3 AS c;                              -- 3.333 (was 3)
SELECT CAST(price AS INTEGER) / 2 AS c FROM t;   -- 2.5   (the cast narrows the OPERAND to 5,
                                                 --        it does not make the division integral)
Where this puts us. / is always-decimal in MySQL, DuckDB and most analytics engines; PostgreSQL is the outlier that truncates when both operands are integers. We follow MySQL and DuckDB, and the reason is not taste: a query containing a JOIN evaluates its SELECT-list expressions in DuckDB, so before 0.24.0 the same n / m answered 3 without a JOIN and 3.5 with one. The two paths now agree.

A computed column declared DOUBLE now stores a double. CREATE TABLE u (n INTEGER, m INTEGER, c DOUBLE SCRIPT AS (n / m)) stored 3.0 for 7/2 before 0.24.0; it stores 3.5.

⚠️ There is no truncating-division operator: write (a / b)::INTEGER. Other engines spell truncation DIV (MySQL) or // (DuckDB); this engine has neither. Since 0.24.0 a :: cast applies to arithmetic and truncates toward zero: (n / m)::INTEGER is 3 for 7 / 2 and -3 for -7 / 2, and NULL for a zero divisor, in a search and in a computed column. The other obvious rewrites are parse errors, because an arithmetic expression is not accepted as the operand of a function, of a CAST or of a CASE branch:
CAST(a / b AS INTEGER)     CAST(a + b AS INTEGER)     CAST((a / b) AS INTEGER)   -- ✗ rejected
FLOOR(a / b)               ABS(a / b)                 COALESCE(a / b, 0)         -- ✗ rejected
(a / b)::INTEGER           FLOOR(x)                   a / NULLIF(b, 0)           -- ✓ accepted
The rule is directional: f(<arithmetic>) is rejected, <arithmetic> f(…) is fine. It is not specific to division — any arithmetic operand is refused — and parenthesising does not help.

% (MOD) is unaffected by this rule and keeps deriving its type from its operands — see the % section below, including what it does with a zero divisor.

Division by Zero:

Since 0.24.0 the engine handles it for you: a zero divisor yields NULL — verified on real Elasticsearch in a projection, in a predicate and in a computed column (an ingest pipeline).

⚠️ Aggregations are not covered by that verification. A division of two aggregates (SUM(a) / SUM(b), HAVING SUM(a) / SUM(b) > 1) is rendered as an Elasticsearch bucket_script / bucket_selector, which is a different execution context and has not been tested against a zero divisor. Guard it in SQL if a bucket can have a zero denominator.

-- no guard needed
SELECT total / order_count AS ratio FROM statistics;   -- NULL where order_count = 0

Before 0.24.0 none of that was NULL, and the failure depended on the operand types: integer division threw (a search answered HTTP 400 arithmetic_exception: / by zero, and an ingest pipeline silently left the computed column out of the document), while floating division produced Infinity — which Elasticsearch refuses to index, so the whole document was rejected.

⚠️ One limitation to know about, older than this rule and not fixed by it: ORDER BY <arithmetic> over nullable columns fails. A script sort is typed number, and Elasticsearch rejects a sort script that can return null — which any arithmetic over a nullable column can, whether from a missing value or from a zero divisor. Measured identically before and after 0.24.0. Sort by a plain column, or by a computed column.

You do not need NULLIF for a division: write total / order_count. It works too: total / NULLIF(order_count, 0) answers NULL on the rows where order_count is 0 or missing, in a search and in a computed column. Before 0.24.0 it threw a null_pointer_exception in a search on exactly those rows, and silently dropped the computed column in an ingest pipeline.

⚠️ Corrected in 0.24.0: the other guards this page used to recommend do not parse. As above, an arithmetic expression is not accepted as the operand of a function, of a CAST or of a CASE branch, so all three of these are parse errors, not slower alternatives — they were published in error and are measured rejections today:

-- ✗ parse error: "')' expected but '/' found"
SELECT COALESCE(total / NULLIF(order_count, 0), 0) AS ratio FROM statistics;

-- ✗ parse error: "'END' expected but '/' found"
SELECT CASE WHEN order_count != 0 THEN total / order_count ELSE 0 END AS ratio FROM statistics;

-- ✗ parse error: "')' expected but '/' found" -- an arithmetic operand inside a function
SELECT FLOOR(total / order_count) AS whole FROM statistics;

Write the division on its own instead, and let the engine return NULL:

-- ✓ percentage, with the zero case NULL
SELECT passed / total * 100 AS pass_rate FROM exam_results;

-- ✓ split cost
SELECT total_cost / num_people AS cost_per_person FROM expenses;

Operator: `%` (MOD)

Description:
Remainder/modulo operator. Unlike /, % takes its result type from its operands — two integers give an integer.

Syntax:

expr1 % expr2

Inputs:

expr1, expr2 - Integer expressions

Output:

Integer (remainder of division)

⚠️ A zero divisor is NOT guarded for % by itself. The 0.24.0 rule that turns a / 0 into NULL covers / only, and a bare % has two distinct failures:

integer operands — a % 0 throws. A search fails with HTTP 400 arithmetic_exception: / by zero; in a computed column the ingest processor's ignore_failure swallows it and the column is simply absent from the indexed document.

floating operands — a % 0 is NaN, and Elasticsearch refuses to index a non-finite number, so the whole document is rejected (document_parsing_exception: [double] supports only finite values). This is the same data-loss failure the / guard was shipped to fix.

Guard it with NULLIF: write a % NULLIF(b, 0). It answers NULL on the rows where b is 0 or missing and the remainder everywhere else, for integer and floating operands alike, in a search and in a computed column. Before 0.24.0 it threw a null_pointer_exception on exactly the rows the guard is for. The other guards one might write are parse errors: CASE WHEN b != 0 THEN a % b END, COALESCE(a % b, 0) and FLOOR(a % b) — an arithmetic expression is not accepted as the operand of a function, of a CAST or of a CASE branch (see the note under / above; the restriction is not specific to division).

Examples:

Basic Modulo:

-- Get remainder
SELECT 10 % 3 AS remainder;
-- Result: 1

-- Bucket users by ID
SELECT id % 10 AS bucket FROM users;

-- Check if number is even
SELECT 
  number,
  CASE WHEN number % 2 = 0 THEN 'Even' ELSE 'Odd' END AS parity
FROM numbers;

Practical Examples:

-- Distribute data across shards
SELECT 
  user_id,
  user_id % 5 AS shard_id
FROM users;

-- Find every Nth record
SELECT * FROM logs
WHERE log_id % 100 = 0;  -- Every 100th record

-- Cycle through values
SELECT 
  day_number,
  day_number % 7 AS day_of_week
FROM calendar;

-- Alternate row colors (even/odd; `row_number` is also a function name: quote it)
SELECT 
  `row_number`,
  CASE WHEN `row_number` % 2 = 0 THEN 'even-row' ELSE 'odd-row' END AS css_class
FROM data_table;

With Negative Numbers:

-- Modulo with negative numbers
SELECT -10 % 3 AS result;
-- Result: -1 (sign follows dividend)

SELECT 10 % -3 AS result;
-- Result: 1

Date arithmetic: `+` and `-` with a `DATE`, `TIMESTAMP` or `DATETIME`

Description:
Since 0.24.0, + and - follow the rules SQL engines apply to dates, in every place a script runs: a row SELECT, a WHERE, a per-group calculation over aggregates (MAX(d) - MIN(d) AS x) or one over the whole table with no GROUP BY, a HAVING over its alias, a computed column (SCRIPT AS) and a materialized view.

Expression	Result	Type
`DATE - DATE`	the calendar days between the two dates	`BIGINT`
`TIMESTAMP - TIMESTAMP`, or a `DATE` and a `TIMESTAMP`	the milliseconds between the two, in days (`1.5` is a day and a half)	`DOUBLE`
`DATE + n`, `DATE - n`, `n` a whole number (`INT`, `BIGINT`, ...)	the date `n` days later / earlier	`DATE`
`DATE + n`, `DATE - n`, `n` a fractional number (`DOUBLE`, `REAL`, `DECIMAL`)	`n` days later / earlier, the fraction as a time of day	`TIMESTAMP`
`TIMESTAMP + n`, `TIMESTAMP - n`, any number `n`	`n` days later / earlier	`TIMESTAMP`
`n + temporal`	the same as `temporal + n`	as above
a temporal and a string literal that is a number (`'30'`, `'1.5'`)	the same as with that number (elasticsql's own rule, not PostgreSQL's: see below)	as above
a temporal and `NULL`	`NULL`	the type above, for a typed `NULL` (`CAST(NULL AS DATE) - d` is a `BIGINT`)
two temporals added, a temporal under `*`, `/` or `%`, a number minus a temporal, a temporal combined with any other string or a boolean	a type error, refused before anything runs	—

DATETIME behaves as TIMESTAMP. Days are UTC calendar days: a DATE operand is the UTC day it falls in, so DATE - DATE is the same number as DATEDIFF(date1, date2).

An operand's type is the type it produces: a column's declared type; an aggregate's own type (COUNT is a BIGINT, AVG a DOUBLE, SUM the column's numeric type, MIN, MAX, FIRST_VALUE and LAST_VALUE the column's type); CASE, COALESCE, NULLIF, GREATEST and LEAST the common type of their branches or arguments. So MAX(order_date) - AVG(delay) is a TIMESTAMP, COUNT(order_date) - 1 a number, and CASE WHEN express THEN ship_date ELSE due_date END - order_date and GREATEST(ship_date, due_date) - order_date numbers of days (GREATEST and LEAST compare dates and timestamps too: see GREATEST).

The engines this follows:

PostgreSQL, DuckDB, Oracle and Snowflake subtract two dates into a number of days; Oracle, whose DATE carries a time of day, answers fractional days.
PostgreSQL, DuckDB, Oracle and BigQuery add a number of days to a date; Oracle turns the fraction of DATE + 1.5 into a time of day.
PostgreSQL, DuckDB, Oracle, Trino, Snowflake and SQL Server refuse the other combinations.
A number written as a string beside a date or a timestamp (order_date + '30') is elasticsql's own rule, not PostgreSQL's: PostgreSQL refuses order_date + '1' (operator is not unique: date + unknown), reads the '1' of order_date - '1' as a date and refuses it (invalid input syntax for type date), and reads created_at + '1' as one second.

Examples:

-- Days between two dates
SELECT ship_date - order_date AS days_to_ship FROM orders;
-- 2024-01-31 -> 2024-02-01 is 1; 2024-02-28 -> 2024-03-01 is 2 (2024 is a leap year)

-- A date moved by days
SELECT due_date + 30 AS reminder FROM invoices;          -- DATE
SELECT due_date + 1.5 AS reminder_at FROM invoices;      -- TIMESTAMP, 12:00 on the next day

-- Fractional days between two instants
SELECT closed_at - opened_at AS days_open FROM tickets;  -- 1.25 is a day and six hours

-- A filter relative to today
SELECT * FROM orders WHERE order_date > CURRENT_DATE - 7;

-- Per group, and in HAVING through the alias
SELECT customer, MAX(order_date) - MIN(order_date) AS span
FROM orders GROUP BY customer HAVING span > 30;

-- A computed column
CREATE TABLE tickets (
  id KEYWORD, opened_at TIMESTAMP, closed_at TIMESTAMP,
  days_open DOUBLE SCRIPT AS (closed_at - opened_at),
  PRIMARY KEY (id)
);

Refused combinations are refused by name, with the operator, the two operand types and the remedy:

SELECT order_date * 2 FROM orders;
-- Type mismatch: operator * cannot be applied to DATE and BIGINT in expression: order_date * 2;
-- subtract two dates for the days between them, or add or subtract a number of days

Notes:

A temporal operand is recognised by its declared type: Elasticsearch stores DATE, TIMESTAMP and DATETIME alike as a date, and the declaration is what tells a date from a timestamp. A TIME operand is not covered by these rules.
A date field elasticsql did not declare — an index created by a bulk load, Logstash or any other client, or a field a bulk load added to a table elasticsql created — is a TIMESTAMP: DESCRIBE TABLE reports it so, a CREATE TABLE ... AS SELECT declares it so, and it follows the TIMESTAMP rules (created_at + 1 keeps the time of day, closed_at - opened_at is fractional). A column declared DATE through elasticsql stays a DATE.
A string literal is read by what it is combined or compared with, as PostgreSQL reads one: order_date - '2024-01-01' is the days between two dates, qty + '1' adds the number 1, and in a comparison — =, <>, <, <=, >, >=, BETWEEN, IN, CASE x WHEN v, NULLIF, a CASE condition — a literal beside a number is the number it spells, beside a DATE the date it spells (the date of a date-time literal), beside a TIMESTAMP the instant it spells ('2024-01-31' is its midnight), and beside a BOOLEAN the boolean it spells. Beside a whole-number type (TINYINT, SMALLINT, INT, BIGINT) only a whole number is read: '1.5' and '1.0' are no integer, so COALESCE(qty, 0) = '1.5' or qty + '1.0' over an integer qty is refused by name, as PostgreSQL refuses it (invalid input syntax for type integer); beside DOUBLE, REAL or DECIMAL, any number is read. (A WHERE comparison with a bare integer column, WHERE qty = '1.5', is the exception: it runs as a query on the column's mapping, and Elasticsearch reads the literal.) A DATE and a TIMESTAMP compare, the DATE being the instant its day starts at. Any other literal is text, and comparing it with a number or a date is refused by name. A number written as a string beside a date or a timestamp in + or - (order_date + '30') is not read as PostgreSQL reads it: elasticsql counts it as that number of days, whatever its fraction, which PostgreSQL does not (see "The engines this follows" above).
An index pattern or a comma list (FROM logs-*, FROM orders_2024, orders_2025) answers like a single index: every field its indices map alike has its type, read once per pattern from the merged mapping. ⚠️ A field two of them map differently (a date in one index, a keyword in another) has no type: nothing is refused on it and Elasticsearch answers for it — per group, MAX(order_date) - MIN(order_date) over such a field answers milliseconds rather than days. Every other field of the pattern keeps its type.
Type errors are checked once the column types are known, when the statement runs and the mapping is read, and before anything is sent to Elasticsearch: WHERE due_date + 7 > CURRENT_DATE runs, SELECT order_date * 2 is refused. With no type to read (a field the indices of a pattern map differently, a mapping that cannot be read), nothing is refused on types.
In a per-group calculation, a date result comes back as the same number a date aggregate does (MAX(order_date)): milliseconds since the epoch. CURRENT_DATE and NOW() there are the time the statement runs (CURRENT_DATE - MAX(order_date)); a materialized view refuses them.
An explicit cast applies to the result, and a NULL stays NULL: (ship_date - order_date)::TIMESTAMP converts the number of days as any number is converted to a TIMESTAMP — as milliseconds since the epoch — and (quantity + ratio)::BIGINT truncates, as CAST(3.25 AS BIGINT) does.
INTERVAL arithmetic (order_date - INTERVAL 7 DAY) and DATEDIFF / TIMESTAMPDIFF are unchanged.
⚠️ On Elasticsearch 6.8, a date column compared with another date column (WHERE ship_date > order_date), or with a date function of one (WHERE ship_date > DATE_TRUNC(order_date, MONTH)), fails with a 400 (failed to parse date field); date arithmetic on one side runs: WHERE ship_date > order_date + 0. Elasticsearch 8 answers all three.

Before 0.24.0: MAX(d) - MIN(d) answered milliseconds and was typed NUMERIC; + and - with a date failed in Elasticsearch at row level — except a date plus a string, which concatenated the two — and per group MAX(d) + 1 failed while MAX(d) + 1.5 added 1.5 milliseconds; CURRENT_DATE - 7 was refused when the statement was parsed; and a computed column over date arithmetic stored epoch-millisecond arithmetic or rejected the document at ingest.

Comparison Operators

Operator: `=`

Description:
Equality comparison.

Syntax:

expr1 = expr2

Inputs:

expr1, expr2 - Any comparable types

Return type:

BOOLEAN

Examples:

Basic Equality:

-- Compare values (a comparison is a condition: in the SELECT list, write it in a CASE)
SELECT CASE WHEN 5 = 5 THEN 1 ELSE 0 END AS result;
-- Result: 1

-- Filter by department
SELECT * FROM emp WHERE department = 'IT';

-- Compare columns
SELECT * FROM orders
WHERE customer_id = shipping_customer_id;

-- Columns of incompatible types (id KEYWORD, n INT) are refused by name once the column types
-- are known, rather than answering no rows
SELECT * FROM t WHERE id = n;
-- Error: Type mismatch: 'KEYWORD' is not compatible with 'INT' in expression: id = n

-- Cast one side to the other's type
SELECT * FROM t WHERE id = CAST(n AS VARCHAR);

String Comparison:

-- Case-sensitive string comparison
SELECT * FROM users WHERE username = 'john_doe';

-- Compare with column
SELECT * FROM products
WHERE category = 'Electronics';

Numeric Comparison:

-- Integer equality
SELECT * FROM products WHERE stock_quantity = 0;

-- Decimal equality
SELECT * FROM orders WHERE total_amount = 99.99;

Date Comparison:

-- Date equality
SELECT * FROM orders WHERE order_date = '2025-01-10';

-- Timestamp equality
SELECT * FROM events 
WHERE event_timestamp = '2025-01-10 14:30:00';

NULL Handling:

-- NULL comparison always returns NULL (not true or false)
SELECT * FROM emp WHERE manager = NULL;  -- Returns no rows!

-- Use IS NULL instead
SELECT * FROM emp WHERE manager IS NULL;

Operator: `<>`, `!=`

Description:
Inequality comparison (both synonyms supported).

Syntax:

expr1 <> expr2
expr1 != expr2

Inputs:

expr1, expr2 - Any comparable types

Return type:

BOOLEAN

Examples:

Basic Inequality:

-- Not equal (a comparison is a condition: in the SELECT list, write it in a CASE)
SELECT CASE WHEN 5 <> 3 THEN 1 ELSE 0 END AS result;
-- Result: 1

SELECT CASE WHEN 5 != 3 THEN 1 ELSE 0 END AS result;
-- Result: 1

-- Filter by status
SELECT * FROM emp WHERE status <> 'terminated';
SELECT * FROM emp WHERE status != 'terminated';

String Inequality:

-- Exclude specific values
SELECT * FROM products WHERE category != 'Discontinued';

-- Multiple exclusions (use NOT IN instead)
SELECT * FROM orders 
WHERE status <> 'cancelled' 
  AND status <> 'refunded';

Numeric Inequality:

-- Not equal to zero
SELECT * FROM products WHERE stock_quantity != 0;

-- Exclude specific value
SELECT * FROM users WHERE age <> 18;

NULL Handling:

-- NULL inequality returns NULL (not true)
SELECT * FROM emp WHERE manager != NULL;  -- Returns no rows!

-- Use IS NOT NULL instead
SELECT * FROM emp WHERE manager IS NOT NULL;

Operator: `<`, `<=`, `>`, `>=`

Description:
Relational comparisons.

Syntax:

expr1 < expr2   -- Less than
expr1 <= expr2  -- Less than or equal
expr1 > expr2   -- Greater than
expr1 >= expr2  -- Greater than or equal

Inputs:

expr1, expr2 - Comparable types (numeric, string, date, etc.)

Return type:

BOOLEAN

Examples:

Numeric Comparisons:

-- Greater than
SELECT * FROM products WHERE price > 100;

-- Less than or equal
SELECT * FROM users WHERE age <= 18;

-- Age range
SELECT * FROM emp WHERE age >= 21 AND age < 65;

-- Between alternative
SELECT * FROM products 
WHERE price >= 50 AND price <= 100;

String Comparisons:

-- Lexicographic comparison
SELECT * FROM users WHERE username > 'M';  -- Names starting with M-Z

-- Alphabetical range
SELECT * FROM products 
WHERE name >= 'A' AND name < 'D';

Date Comparisons:

-- After specific date
SELECT * FROM orders WHERE order_date > '2025-01-01';

-- Before or on date
SELECT * FROM events WHERE event_date <= CURRENT_DATE;

-- Date range
SELECT * FROM logs
WHERE log_date >= '2025-01-01' 
  AND log_date < '2025-02-01';

Timestamp Comparisons:

-- Recent records
SELECT * FROM activities
WHERE created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR);

-- Within time range
SELECT * FROM events
WHERE event_time >= '2025-01-10 09:00:00'
  AND event_time <= '2025-01-10 17:00:00';

Operator: `IN`

Description:
Membership in a set of literal or numeric values, or results of subquery (subquery support depends on implementation).

Syntax:

expr IN (value1, value2, ...)
expr IN (subquery)

Inputs:

expr - Expression to test
value1, value2, ... - List of values
subquery - Subquery returning single column

Return type:

BOOLEAN

Examples:

Basic IN with Literals:

-- String values
SELECT * FROM emp WHERE department IN ('Sales', 'IT', 'HR');

-- Numeric values
SELECT * FROM emp WHERE status IN (1, 2);

-- Single value (equivalent to =)
SELECT * FROM products WHERE category IN ('Electronics');

Multiple Value Types:

-- Integer list
SELECT * FROM orders WHERE order_id IN (100, 101, 102, 103);

-- String list
SELECT * FROM users 
WHERE country IN ('US', 'CA', 'MX', 'UK');

-- Date list
SELECT * FROM events
WHERE event_date IN ('2025-01-01', '2025-01-15', '2025-01-31');

With Subquery:

-- Subquery returning IDs
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'premium'
);

-- Nested subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

Empty List:

-- ✗ parse error: an IN list holds at least one value
SELECT * FROM products WHERE id IN ();

NULL Handling:

-- ✗ parse error: NULL is not accepted in an IN list
SELECT * FROM users WHERE status IN ('active', NULL);

-- Keep the rows whose status is NULL as well
SELECT * FROM users WHERE status = 'active' OR status IS NULL;

-- Column with NULL
SELECT * FROM users WHERE email IN ('test@example.com');
-- Rows with NULL email are not matched

Operator: `NOT IN`

Description:
Negated membership.

Syntax:

expr NOT IN (value1, value2, ...)
expr NOT IN (subquery)

Inputs:

expr - Expression to test
value1, value2, ... - List of values to exclude
subquery - Subquery returning single column

Return type:

BOOLEAN

Examples:

Basic NOT IN:

-- Exclude departments
SELECT * FROM emp WHERE department NOT IN ('HR', 'Legal');

-- Exclude statuses
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

-- Exclude IDs
SELECT * FROM products WHERE product_id NOT IN (1, 2, 3);

With Subquery:

-- Exclude customers who have orders
SELECT * FROM customers
WHERE id NOT IN (
  SELECT DISTINCT customer_id FROM orders
);

-- Exclude inactive categories
SELECT * FROM products
WHERE category_id NOT IN (
  SELECT id FROM categories WHERE active = false
);

NULL Handling (Important!):

-- ✗ parse error: NULL is not accepted in a NOT IN list
SELECT * FROM users WHERE id NOT IN (1, 2, NULL);

-- Safe alternative: filter NULLs in subquery
SELECT * FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Or use NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Operator: `BETWEEN ... AND ...`

Description:
Checks if an expression lies between two boundaries (inclusive).

For numeric expressions, BETWEEN works as standard SQL.
For distance expressions (ST_DISTANCE), it supports units (m, km, mi, etc.).

Syntax:

expr BETWEEN lower_bound AND upper_bound

Inputs:

expr - Expression to test
lower_bound, upper_bound - Boundary values (inclusive)

Return type:

BOOLEAN

Examples:

Numeric BETWEEN:

-- Age range
SELECT age FROM users WHERE age BETWEEN 18 AND 30;
-- Equivalent to: age >= 18 AND age <= 30

-- Price range
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- Quantity range
SELECT * FROM inventory WHERE stock_quantity BETWEEN 10 AND 100;

Temporal BETWEEN:

-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

-- With date functions
SELECT * FROM users
WHERE createdAt BETWEEN CURRENT_DATE - INTERVAL 1 MONTH AND CURRENT_DATE;

-- Complex temporal range
SELECT * FROM users
WHERE createdAt BETWEEN CURRENT_DATE - INTERVAL 1 MONTH AND CURRENT_DATE
  AND lastUpdated BETWEEN LAST_DAY('2025-09-11'::DATE) AND DATE_TRUNC(CURRENT_TIMESTAMP, DAY);

-- Timestamp range
SELECT * FROM events
WHERE event_timestamp BETWEEN '2025-01-10 00:00:00' AND '2025-01-10 23:59:59';

Distance BETWEEN (Geospatial):

Using meters (default):

-- Distance in meters
SELECT id FROM locations 
WHERE ST_DISTANCE(POINT(-70.0, 40.0), toLocation) BETWEEN 4000 AND 5000;
-- Finds locations between 4km and 5km away

With explicit units:

-- Distance with km units
SELECT id FROM locations 
WHERE ST_DISTANCE(POINT(-70.0, 40.0), toLocation) BETWEEN 4000 km AND 5000 km;

-- Distance with miles (a distance with a unit is a whole number: `2.5 mi` does not parse)
SELECT id FROM locations 
WHERE ST_DISTANCE(POINT(-70.0, 40.0), toLocation) BETWEEN 2 mi AND 4 mi;

Elasticsearch Optimization:

👉 In Elasticsearch translation, distance BETWEEN queries are optimized into a combination of:

A script filter for the lower bound

A geo_distance query for the upper bound (native ES optimization)

String BETWEEN:

-- Lexicographic range
SELECT * FROM products WHERE name BETWEEN 'A' AND 'D';
-- Names starting with A, B, or C

-- Date strings (if stored as strings)
SELECT * FROM logs WHERE log_date BETWEEN '2025-01' AND '2025-03';

NOT BETWEEN:

-- Outside range
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;
-- Equivalent to: price < 50 OR price > 100

-- Exclude date range
SELECT * FROM orders
WHERE order_date NOT BETWEEN '2024-12-20' AND '2025-01-05';

NULL Handling:

-- NULL expression returns NULL (not true or false)
SELECT * FROM products WHERE NULL BETWEEN 10 AND 20;
-- Returns no rows

-- Column with NULL
SELECT * FROM products WHERE price BETWEEN 10 AND 20;
-- Rows with NULL price are excluded

Operator: `IS NULL`

Description:
Null check predicate.

Syntax:

expr IS NULL

Inputs:

expr - Expression to test for NULL

Return type:

BOOLEAN

Examples:

Basic NULL Check:

-- Find rows with NULL manager
SELECT * FROM emp WHERE manager IS NULL;

-- Find products without description
SELECT * FROM products WHERE description IS NULL;

-- Find users without email
SELECT * FROM users WHERE email IS NULL;

Multiple NULL Checks:

-- Check multiple columns
SELECT * FROM contacts
WHERE phone IS NULL AND email IS NULL;

-- Either column is NULL
SELECT * FROM users
WHERE first_name IS NULL OR last_name IS NULL;

In SELECT:

-- Flag NULL values
SELECT 
  name,
  email,
  CASE WHEN email IS NULL THEN 'No Email' ELSE 'Has Email' END AS email_status
FROM users;

-- Count NULLs
SELECT 
  COUNT(*) AS total_rows,
  COUNT(email) AS rows_with_email,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS rows_without_email
FROM users;

With COALESCE:

-- Provide default for NULL
SELECT 
  name,
  COALESCE(email, 'no-email@example.com') AS email
FROM users
WHERE email IS NULL;

Operator: `IS NOT NULL`

Description:
Negated null check.

Syntax:

expr IS NOT NULL

Inputs:

expr - Expression to test for non-NULL

Return type:

BOOLEAN

Examples:

Basic NOT NULL Check:

-- Find rows with manager assigned
SELECT * FROM emp WHERE manager IS NOT NULL;

-- Find products with description
SELECT * FROM products WHERE description IS NOT NULL;

-- Find users with email
SELECT * FROM users WHERE email IS NOT NULL;

Multiple NOT NULL Checks:

-- Both columns must have values
SELECT * FROM contacts
WHERE phone IS NOT NULL AND email IS NOT NULL;

-- At least one column has value
SELECT * FROM users
WHERE first_name IS NOT NULL OR last_name IS NOT NULL;

Data Quality Checks:

-- Complete records only
SELECT * FROM orders
WHERE customer_id IS NOT NULL
  AND order_date IS NOT NULL
  AND total_amount IS NOT NULL;

-- Count complete records
SELECT 
  COUNT(*) AS total,
  COUNT(CASE WHEN email IS NOT NULL AND phone IS NOT NULL THEN 1 END) AS complete_contacts
FROM users;

In Aggregations:

-- Aggregate only non-NULL values
SELECT 
  category,
  COUNT(*) AS total_products,
  COUNT(CASE WHEN price IS NOT NULL THEN 1 END) AS products_with_price
FROM products
GROUP BY category;

Operator: `LIKE`

Description:
Pattern match using % and _.

% matches zero or more characters (converted to .* in regex)
_ matches exactly one character (converted to . in regex)

Syntax:

expr LIKE pattern

Inputs:

expr - String expression to test
pattern - Pattern string with % and _ wildcards

Return type:

BOOLEAN

Examples:

Basic LIKE Patterns:

-- Starts with
SELECT * FROM emp WHERE name LIKE 'Jo%';
-- Matches: 'John', 'Joe', 'Joseph', etc.

-- Ends with
SELECT * FROM products WHERE name LIKE '%phone';
-- Matches: 'iPhone', 'smartphone', 'telephone', etc.

-- Contains
SELECT * FROM articles WHERE title LIKE '%tutorial%';
-- Matches any title containing 'tutorial'

-- Exact length with _
SELECT * FROM codes WHERE code LIKE '___';
-- Matches exactly 3 characters: 'ABC', '123', etc.

Complex Patterns:

-- Starts with and ends with
SELECT * FROM products WHERE name LIKE 'iPhone%Pro';
-- Matches: 'iPhone 14 Pro', 'iPhone 15 Pro Max', etc.

-- Multiple wildcards
SELECT * FROM emails WHERE address LIKE '%@%.com';
-- Matches emails ending with .com

-- Single character wildcard
SELECT * FROM users WHERE username LIKE 'user_0_';
-- Matches: 'user_01', 'user_02', ..., 'user_99'

-- Pattern with specific positions
SELECT * FROM phone_numbers WHERE number LIKE '555-____';
-- Matches: '555-1234', '555-5678', etc.

Case Sensitivity:

-- Case-insensitive (depends on collation)
SELECT * FROM users WHERE name LIKE 'john%';
-- May or may not match 'JOHN', 'John', 'john'

-- Force case-insensitive with LOWER (the pattern is a literal: write it in lower case)
SELECT * FROM users WHERE LOWER(name) LIKE 'john%';
-- Matches all case variations

NOT LIKE:

-- Exclude pattern
SELECT * FROM products WHERE name NOT LIKE '%discontinued%';

-- Exclude multiple patterns
SELECT * FROM articles 
WHERE title NOT LIKE '%draft%' 
  AND title NOT LIKE '%temp%';

Escaping Special Characters:

-- ✗ parse error: the ESCAPE clause is not supported
SELECT * FROM products WHERE name LIKE '100\% cotton' ESCAPE '\';

-- A literal % or _: use RLIKE, where both are ordinary characters
SELECT * FROM products WHERE name RLIKE '100% cotton';
-- Matches: '100% cotton'

SELECT * FROM codes WHERE code RLIKE 'CODE_123';
-- Matches: 'CODE_123'

Performance Note:

-- Leading wildcard prevents index usage
SELECT * FROM products WHERE name LIKE '%phone';  -- Slow

-- Prefix search can use index
SELECT * FROM products WHERE name LIKE 'phone%';  -- Fast

-- Consider full-text search for complex patterns
SELECT * FROM products WHERE MATCH(name) AGAINST ('phone');

Operator: `RLIKE`

Description:
Regular-expression match (Java regex semantics).

Syntax:

expr RLIKE pattern

Inputs:

expr - String expression to test
pattern - Regular expression pattern (Java regex syntax)

Return type:

BOOLEAN

Examples:

Basic Regex Patterns:

-- Email validation
SELECT * FROM users WHERE email RLIKE '.*@example\\.com$';
-- Matches emails ending with @example.com

-- Phone number pattern
SELECT * FROM contacts WHERE phone RLIKE '^\\d{3}-\\d{3}-\\d{4}$';
-- Matches: '555-123-4567'

-- Starts with pattern
SELECT * FROM products WHERE name RLIKE '^iPhone';
-- Matches names starting with 'iPhone'

-- Ends with pattern
SELECT * FROM files WHERE filename RLIKE '\\.(jpg|png|gif)$';
-- Matches image files

Character Classes:

-- Alphanumeric
SELECT * FROM codes WHERE code RLIKE '^[A-Z0-9]+$';
-- Matches uppercase letters and numbers only

-- Digits only
SELECT * FROM ids WHERE id RLIKE '^\\d+$';
-- Matches numeric IDs

-- Letters only
SELECT * FROM names WHERE name RLIKE '^[a-zA-Z]+$';
-- Matches alphabetic names only

Quantifiers:

-- Exact count
SELECT * FROM zipcodes WHERE code RLIKE '^\\d{5}$';
-- Matches exactly 5 digits: '12345'

-- Range
SELECT * FROM passwords WHERE password RLIKE '^.{8,20}$';
-- Matches 8 to 20 characters

-- One or more
SELECT * FROM tags WHERE tag RLIKE '^#[a-z]+$';
-- Matches hashtags: '#tag', '#example'

-- Zero or more
SELECT * FROM urls WHERE url RLIKE '^https?://.*';
-- Matches http:// or https:// URLs

Grouping and Alternation:

-- Multiple options
SELECT * FROM products WHERE name RLIKE 'iPhone|iPad|iPod';
-- Matches any Apple i-device

-- Grouped patterns
SELECT * FROM users WHERE email RLIKE '^(admin|support|info)@.*';
-- Matches emails starting with admin@, support@, or info@

-- Complex grouping
SELECT * FROM codes WHERE code RLIKE '^(US|CA|MX)-[0-9]{4}$';
-- Matches: 'US-1234', 'CA-5678', 'MX-9012'

Anchors:

-- Start of string
SELECT * FROM usernames WHERE username RLIKE '^admin';
-- Matches usernames starting with 'admin'

-- End of string
SELECT * FROM emails WHERE email RLIKE '@company\\.com$';
-- Matches emails ending with @company.com

-- Whole string match
SELECT * FROM codes WHERE code RLIKE '^[A-Z]{3}-[0-9]{4}$';
-- Matches exactly: 'ABC-1234'

Advanced Patterns:

-- URL validation
SELECT * FROM links 
WHERE url RLIKE '^https?://[a-zA-Z0-9.-]+\\.[a-z]{2,}(/.*)?$';

-- IPv4 address
SELECT * FROM servers 
WHERE ip_address RLIKE '^([0-9]{1,3}\\.){3}[0-9]{1,3}$';

-- Credit card (basic pattern)
SELECT * FROM payments 
WHERE card_number RLIKE '^[0-9]{4}-[0-9]{4}-[0-9]{4}-[0-9]{4}$';

-- Date format (YYYY-MM-DD)
SELECT * FROM records 
WHERE date_str RLIKE '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

NOT RLIKE:

-- Exclude pattern
SELECT * FROM users WHERE email NOT RLIKE '.*@spam\\.com$';

-- Exclude invalid formats
SELECT * FROM phone_numbers 
WHERE number NOT RLIKE '^[0-9-]+$';

Case Insensitive:

-- Use (?i) flag for case-insensitive
SELECT * FROM products WHERE name RLIKE '(?i)^iphone';
-- Matches: 'iPhone', 'IPHONE', 'iphone', etc.

Comparison with LIKE:

-- LIKE (simpler, limited wildcards)
SELECT * FROM products WHERE name LIKE 'iPhone%';

-- RLIKE (powerful, full regex)
SELECT * FROM products WHERE name RLIKE '^iPhone (1[0-5]|[0-9]) (Pro|Max).*';
-- Matches: 'iPhone 14 Pro', 'iPhone 15 Pro Max', etc.

Logical Operators

Operator: `AND`

Description:
Logical conjunction. Returns true only if both operands are true.

Syntax:

condition1 AND condition2

Inputs:

condition1, condition2 - Boolean expressions

Return type:

BOOLEAN

Truth Table:

A	B	A AND B
true	true	true
true	false	false
false	true	false
false	false	false
true	NULL	NULL
false	NULL	false
NULL	true	NULL
NULL	false	false
NULL	NULL	NULL

Examples:

Basic AND:

-- Both conditions must be true
SELECT * FROM emp WHERE dept = 'IT' AND salary > 50000;

-- Multiple AND conditions
SELECT * FROM products
WHERE category = 'Electronics'
  AND price > 100
  AND in_stock = true;

Combining Different Comparisons:

-- Range with AND
SELECT * FROM users
WHERE age >= 18 AND age <= 65;

-- Multiple field checks
SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND customer_id IS NOT NULL;

AND with Complex Expressions:

-- With calculations
SELECT * FROM employees
WHERE salary + bonus > 100000
  AND department IN ('Sales', 'IT');

-- With pattern matching
SELECT * FROM products
WHERE name LIKE '%Pro%'
  AND price BETWEEN 500 AND 2000;

NULL Handling:

-- NULL in AND expression
SELECT * FROM users
WHERE is_active = true AND email IS NOT NULL;
-- Only returns rows where both conditions are true

-- NULL propagation
SELECT * FROM products
WHERE price > 100 AND discount IS NULL;
-- Returns products with price > 100 and no discount

Chaining Multiple AND:

-- Long AND chain
SELECT * FROM orders
WHERE status = 'completed'
  AND payment_status = 'paid'
  AND shipping_status = 'delivered'
  AND customer_rating >= 4
  AND order_date >= '2025-01-01';

Performance Tip:

-- Put most selective conditions first
SELECT * FROM large_table
WHERE rare_condition = true      -- Filters most rows
  AND common_condition = 'value'  -- Filters fewer rows
  AND another_condition > 100;

Operator: `OR`

Description:
Logical disjunction. Returns true if at least one operand is true.

Syntax:

condition1 OR condition2

Inputs:

condition1, condition2 - Boolean expressions

Return type:

BOOLEAN

Truth Table:

A	B	A OR B
true	true	true
true	false	true
false	true	true
false	false	false
true	NULL	true
false	NULL	NULL
NULL	true	true
NULL	false	NULL
NULL	NULL	NULL

Examples:

Basic OR:

-- At least one condition must be true
SELECT * FROM emp WHERE dept = 'IT' OR dept = 'Sales';

-- Multiple OR conditions
SELECT * FROM products
WHERE category = 'Electronics'
   OR category = 'Computers'
   OR category = 'Phones';

OR with Different Types:

-- Numeric OR
SELECT * FROM users
WHERE age < 18 OR age > 65;

-- String OR
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';

-- Mixed conditions
SELECT * FROM products
WHERE price < 10 OR on_sale = true;

OR with AND (Precedence):

-- AND has higher precedence than OR
SELECT * FROM products
WHERE category = 'Electronics' AND price < 100 OR on_sale = true;
-- Evaluated as: ((category = 'Electronics') AND (price < 100)) OR (on_sale = true)

-- Use parentheses for clarity
SELECT * FROM products
WHERE category = 'Electronics' AND (price < 100 OR on_sale = true);
-- Evaluated as: (category = 'Electronics') AND ((price < 100) OR (on_sale = true))

Since 0.24.0 a condition that combines AND and OR without parentheses is evaluated with this precedence in every clause (WHERE, HAVING, CASE WHEN, DELETE, UPDATE); earlier versions could evaluate it in another order and return other rows without an error.

Multiple OR Conditions:

-- Status check
SELECT * FROM orders
WHERE status = 'cancelled'
   OR status = 'refunded'
   OR status = 'failed';

-- Better: Use IN instead
SELECT * FROM orders
WHERE status IN ('cancelled', 'refunded', 'failed');

OR with NULL:

-- NULL in OR expression
SELECT * FROM users
WHERE email IS NULL OR phone IS NULL;
-- Returns users missing email OR phone (or both)

-- TRUE OR NULL = TRUE
SELECT * FROM products
WHERE in_stock = true OR discount IS NULL;
-- Returns in-stock products regardless of discount

Complex OR Expressions:

-- Combining multiple conditions
SELECT * FROM employees
WHERE (department = 'Sales' AND salary > 80000)
   OR (department = 'IT' AND years_experience > 5)
   OR (is_manager = true);

Performance Consideration:

-- OR can prevent index usage
SELECT * FROM users
WHERE first_name = 'John' OR last_name = 'Doe';
-- May require full table scan

-- Alternative: UNION ALL (if indexes exist)
-- Note: bare UNION (with de-duplication) is not supported and is rejected at
-- parse time — a row matching BOTH predicates appears twice with UNION ALL.
SELECT * FROM users WHERE first_name = 'John'
UNION ALL
SELECT * FROM users WHERE last_name = 'Doe' AND first_name <> 'John';

Operator: `NOT`

Description:
Logical negation. Inverts the boolean value.

Syntax:

NOT condition

Inputs:

condition - Boolean expression

Return type:

BOOLEAN

Truth Table:

A	NOT A
true	false
false	true
NULL	NULL

Examples:

NOT negates the ONE condition after it. It is written before a comparison (NOT price > 100), and after the column for IN, LIKE, RLIKE, BETWEEN and IS NULL (status NOT IN (...), manager IS NOT NULL). A NOT before a parenthesised group or before a bare boolean column is not accepted: write the condition De Morgan's laws give, or compare the column. See Known Limitations.

Basic NOT:

-- Negate a boolean column: compare it
SELECT * FROM emp WHERE active = false;

-- Negate comparison
SELECT * FROM products WHERE NOT price > 100;
-- Same as: WHERE price <= 100

NOT with IN:

-- Exclude values
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

NOT with BETWEEN:

-- Outside range
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;

NOT with LIKE:

-- Exclude pattern
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

-- Multiple NOT LIKE
SELECT * FROM products
WHERE name NOT LIKE '%discontinued%'
  AND name NOT LIKE '%obsolete%';

NOT with IS NULL:

-- Has value
SELECT * FROM emp WHERE manager IS NOT NULL;

-- Both fields have values: NOT (email IS NULL OR phone IS NULL)
SELECT * FROM contacts
WHERE email IS NOT NULL AND phone IS NOT NULL;

NOT with AND/OR:

-- De Morgan's Law: NOT (A AND B) = (NOT A) OR (NOT B)
SELECT * FROM users
WHERE NOT is_active = true OR NOT is_verified = true;

-- De Morgan's Law: NOT (A OR B) = (NOT A) AND (NOT B)
SELECT * FROM products
WHERE NOT category = 'Discontinued' AND NOT in_stock = false;

Double Negation:

-- NOT NOT is the identity: write the condition itself
SELECT * FROM users WHERE is_active = true;
SELECT * FROM products WHERE price > 100;

NOT with Complex Expressions:

-- Orders that don't meet ALL three conditions:
-- NOT (status = 'completed' AND payment_status = 'paid' AND total_amount > 1000)
SELECT * FROM orders
WHERE NOT status = 'completed'
   OR NOT payment_status = 'paid'
   OR NOT total_amount > 1000;

-- Non-Sales employees OR Sales employees earning >= 50000:
-- NOT (department = 'Sales' AND salary < 50000)
SELECT * FROM employees
WHERE NOT department = 'Sales' OR NOT salary < 50000;

NOT with EXISTS:

-- Find customers without orders
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Practical Examples:

-- Active and verified users: NOT (is_active = false OR is_verified = false)
SELECT * FROM users
WHERE NOT is_active = false AND NOT is_verified = false;

-- Products not in specific categories
SELECT * FROM products
WHERE category NOT IN ('Discontinued', 'Clearance', 'Obsolete');

-- Orders not in terminal states
SELECT * FROM orders
WHERE status NOT IN ('completed', 'cancelled', 'refunded');

-- Users without complete profile:
-- NOT (email IS NOT NULL AND phone IS NOT NULL AND address IS NOT NULL)
SELECT * FROM users
WHERE email IS NULL
   OR phone IS NULL
   OR address IS NULL;

NULL Handling:

-- Test NULL with IS NULL / IS NOT NULL, never with = NULL
SELECT * FROM products WHERE discount IS NOT NULL;
SELECT * FROM users WHERE status IS NOT NULL;

Best Practices:

-- Good: Use positive logic when possible
SELECT * FROM users WHERE is_active = true;

-- Avoid: Double negatives
SELECT * FROM users WHERE NOT is_active = false;

-- Good: Use specific operators
SELECT * FROM products WHERE category NOT IN ('A', 'B');

Combining Logical Operators

AND + OR + NOT:

-- Complex business logic
SELECT * FROM orders
WHERE (
    (status = 'pending' AND created_date < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
    OR (status = 'processing' AND priority = 'high')
  )
  AND NOT customer_type = 'blocked'
  AND total_amount > 0;

Precedence Reminder:

NOT (highest)
AND
OR (lowest)

-- Without parentheses (follows precedence)
SELECT * FROM products
WHERE NOT in_stock = true AND price < 100 OR on_sale = true;
-- Evaluated as: ((NOT in_stock = true) AND (price < 100)) OR (on_sale = true)

-- With parentheses (explicit, same meaning)
SELECT * FROM products
WHERE (NOT in_stock = true AND price < 100) OR on_sale = true;

-- With parentheses (different logic)
SELECT * FROM products
WHERE NOT in_stock = true AND (price < 100 OR on_sale = true);
-- Evaluated as: (NOT in_stock = true) AND ((price < 100) OR (on_sale = true))

De Morgan's Laws:

-- NOT (A AND B) = (NOT A) OR (NOT B)
-- NOT before a parenthesised group is not supported: write the right-hand side
SELECT * FROM users
WHERE NOT is_active = true OR NOT is_verified = true;

-- NOT (A OR B) = (NOT A) AND (NOT B)
SELECT * FROM products
WHERE category != 'A' AND category != 'B';
-- Or better:
SELECT * FROM products
WHERE category NOT IN ('A', 'B');

Cast Operators

Operator: `::`

Description:
Provides an alternative syntax to the CAST function. PostgreSQL-style type casting.

Syntax:

expr::TYPE

Inputs:

expr - Expression to convert
TYPE - Target data type (DATE, TIMESTAMP, VARCHAR, INT, DOUBLE, etc.)

Return type:

TYPE

Examples:

Basic Type Casting:

-- String to DATE
SELECT hire_date::DATE FROM emp;

-- String to INT
SELECT '123'::INT AS num;
-- Result: 123

-- String to DOUBLE
SELECT '123.45'::DOUBLE AS num;
-- Result: 123.45

-- INT to VARCHAR
SELECT 12345::VARCHAR AS str;
-- Result: '12345'

Date and Time Casting:

-- String to DATE
SELECT '2025-01-10'::DATE AS d;
-- Result: 2025-01-10

-- String to TIMESTAMP
SELECT '2025-01-10 14:30:00'::TIMESTAMP AS ts;
-- Result: 2025-01-10 14:30:00

-- TIMESTAMP to DATE (`today` is a function name: another alias)
SELECT CURRENT_TIMESTAMP::DATE AS current_day;
-- Result: 2025-10-27

-- Date string with explicit cast
SELECT order_date::DATE FROM orders;

Numeric Casting:

-- INT to DOUBLE
SELECT 100::DOUBLE AS d;
-- Result: 100.0

-- DOUBLE to INT (truncates)
SELECT 123.99::INT AS i;
-- Result: 123

-- String to DECIMAL
SELECT '123.45'::DECIMAL(10, 2) AS dec;
-- Result: 123.45

Boolean Casting:

-- String to BOOLEAN
SELECT 'true'::BOOLEAN AS b;
-- Result: true

SELECT 'false'::BOOLEAN AS b;
-- Result: false

-- INT to BOOLEAN
SELECT 1::BOOLEAN AS b;
-- Result: true

SELECT 0::BOOLEAN AS b;
-- Result: false

In WHERE Clause:

-- Cast for comparison (the literal on the right is cast with CAST)
SELECT * FROM orders
WHERE order_date::DATE >= CAST('2025-01-01' AS DATE);

-- Cast string to number
SELECT * FROM products
WHERE price_str::DOUBLE > 100;

-- Cast to timestamp
SELECT * FROM events
WHERE event_time::TIMESTAMP >= CAST('2025-01-10 00:00:00' AS TIMESTAMP);

In Calculations:

-- A cast is no longer needed to make a division float -- `/` always is (since 0.24.0)
SELECT total / order_count AS average
FROM statistics;

-- Cast for arithmetic
SELECT (price_str::DOUBLE * quantity_str::INT) AS total
FROM order_items;

Chained Casting:

-- Multiple casts
SELECT hire_date::VARCHAR::DATE FROM emp;
-- First to VARCHAR, then to DATE

-- Cast then manipulate
SELECT CONCAT(salary::VARCHAR, ' USD') AS formatted_salary
FROM employees;

Comparison with CAST Function:

-- Using :: operator (PostgreSQL style)
SELECT hire_date::DATE FROM emp;

-- Using CAST function (standard SQL)
SELECT CAST(hire_date AS DATE) FROM emp;

-- Both are equivalent, choose based on preference
-- :: is shorter and more readable
-- CAST is more standard and portable

Complex Examples:

-- ✗ refused: a JOIN condition compares the columns themselves
-- ("... cannot use functions in equality expressions")
SELECT o.*, p.*
FROM orders o
JOIN products p ON o.product_id::VARCHAR = p.product_code;

-- Cast in GROUP BY
SELECT 
  order_date::DATE AS order_day,
  COUNT(*) AS order_count
FROM orders
GROUP BY order_date::DATE;

-- Cast in CASE expression
SELECT 
  product_id,
  CASE 
    WHEN stock_str::INT > 100 THEN 'High'
    WHEN stock_str::INT > 50 THEN 'Medium'
    ELSE 'Low'
  END AS stock_level
FROM inventory;

Error Handling:

-- Invalid cast throws error
SELECT 'not-a-number'::INT;
-- ERROR: Cannot cast 'not-a-number' to INT

-- Use TRY_CAST for safe casting
SELECT TRY_CAST('not-a-number' AS INT);
-- Result: NULL

-- Note: :: operator doesn't have a "try" version
-- Use CAST/TRY_CAST for error handling

NULL Handling:

-- NULL cast returns NULL
SELECT NULL::INT AS result;
-- Result: NULL

-- Cast NULL column
SELECT price::DOUBLE FROM products;
-- NULL prices remain NULL

Best Practices:

-- Good: Use :: for readability
SELECT created_at::DATE FROM users;

-- Good: Use CAST for compatibility
SELECT CAST(created_at AS DATE) FROM users;

-- Good: Cast both sides of comparison
SELECT * FROM orders
WHERE order_date::DATE = CAST('2025-01-10' AS DATE);

-- Avoid: Implicit type conversion (may cause issues)
SELECT * FROM orders WHERE order_date = '2025-01-10';

Summary: Cast Operators

Syntax	Example	Notes
`::TYPE`	`'123'::INT`	PostgreSQL style, shorter
`CAST(expr AS TYPE)`	`CAST('123' AS INT)`	Standard SQL, more portable
`CONVERT(expr, TYPE)`	`CONVERT('123', INT)`	MySQL style (if supported)
`TRY_CAST(expr AS TYPE)`	`TRY_CAST('abc' AS INT)`	Returns NULL on error

When to use :::

PostgreSQL-compatible systems
Quick, readable casts
When you're confident the cast will succeed

When to use CAST:

Maximum SQL portability
When writing cross-database queries
Corporate/enterprise environments

When to use TRY_CAST:

Uncertain data quality
User input
ETL/data import operations
When you want NULL instead of errors

Back to index

Navigation Menu

Repository navigation

operators.md

operators.md

Operators (detailed)

Table of Contents

Math Operators

Operator: `+`

Operator: `-`

Operator: `*`

Operator: `/`

Operator: `%` (MOD)

Date arithmetic: `+` and `-` with a `DATE`, `TIMESTAMP` or `DATETIME`

Comparison Operators

Operator: `=`

Operator: `<>`, `!=`

Operator: `<`, `<=`, `>`, `>=`

Operator: `IN`

Operator: `NOT IN`

Operator: `BETWEEN ... AND ...`

Operator: `IS NULL`

Operator: `IS NOT NULL`

Operator: `LIKE`

Operator: `RLIKE`

Logical Operators

Operator: `AND`

Operator: `OR`

Operator: `NOT`

Combining Logical Operators

Cast Operators

Operator: `::`

Summary: Cast Operators

FilesExpand file tree

operators.md

Latest commit

History

operators.md

File metadata and controls

Operators (detailed)

Table of Contents

Math Operators

Operator: +

Operator: -

Operator: *

Operator: /

Operator: % (MOD)

Date arithmetic: + and - with a DATE, TIMESTAMP or DATETIME

Comparison Operators

Operator: =

Operator: <>, !=

Operator: <, <=, >, >=

Operator: IN

Operator: NOT IN

Operator: BETWEEN ... AND ...

Operator: IS NULL

Operator: IS NOT NULL

Operator: LIKE

Operator: RLIKE

Logical Operators

Operator: AND

Operator: OR

Operator: NOT

Combining Logical Operators

Cast Operators

Operator: ::

Summary: Cast Operators

Operator: `+`

Operator: `-`

Operator: `*`

Operator: `/`

Operator: `%` (MOD)

Date arithmetic: `+` and `-` with a `DATE`, `TIMESTAMP` or `DATETIME`

Operator: `=`

Operator: `<>`, `!=`

Operator: `<`, `<=`, `>`, `>=`

Operator: `IN`

Operator: `NOT IN`

Operator: `BETWEEN ... AND ...`

Operator: `IS NULL`

Operator: `IS NOT NULL`

Operator: `LIKE`

Operator: `RLIKE`

Operator: `AND`

Operator: `OR`

Operator: `NOT`

Operator: `::`