Operators are the small symbols and keywords that do the actual work inside a SQL query. They add numbers, compare values, join conditions together, and match text patterns. Learn the handful that follow and you can read and write the filters, calculations, and searches behind almost every real query.
What this guide covers
An operator takes one or more values and produces a new value. Some, like the minus sign in -salary, work on a single value and are called unary. Most work on two values, one on each side, and are called binary. The families below cover everything a beginner needs. If SQL itself is new to you, start with the SQL fundamentals first, then use this page as your operator reference. The two big engines document every operator in full: the MySQL operator reference and the PostgreSQL functions and operators manual.
Arithmetic operators
Arithmetic operators do maths on numeric values. There are five you will use constantly. They work in the SELECT list to compute new columns and in the WHERE clause to compute conditions.
| Operator | Meaning | Example | Result |
|---|---|---|---|
| + | Addition | 10 + 3 | 13 |
| - | Subtraction | 10 - 3 | 7 |
| * | Multiplication | 10 * 3 | 30 |
| / | Division | 10 / 3 | 3.33 |
| % | Modulo (remainder) | 10 % 3 | 1 |
Here they are calculating derived columns in a real query. Notice how each expression gets an alias so the output column has a readable name:
SELECT name,
price,
quantity,
price * quantity AS line_total,
price * 0.9 AS sale_price,
quantity % 12 AS leftover_in_dozen
FROM order_lines;name price quantity line_total sale_price leftover_in_dozen
Widget 5.00 25 125.00 4.50 1
Gadget 8.00 12 96.00 7.20 0One trap is worth flagging early. In many databases, dividing an integer by an integer gives an integer, so 7 / 2 returns 3, not 3.5. To force a decimal result, make one side a decimal, for example 7 * 1.0 / 2. The modulo operator % returns the remainder after division and is handy for tests like "is this number even", written n % 2 = 0.
Comparison operators
Comparison operators ask a yes or no question about two values and return a truth value. They are the engine of the WHERE clause, where the database keeps only the rows for which the comparison is true. If you want a full tour of filtering, see the guide to the SQL WHERE clause.
| Operator | Meaning | Example | True when |
|---|---|---|---|
| = | Equal to | status = 'paid' | status is exactly paid |
| <> | Not equal to | status <> 'paid' | status is anything but paid |
| < | Less than | price < 100 | price is below 100 |
| > | Greater than | price > 100 | price is above 100 |
| <= | Less than or equal | age <= 18 | age is 18 or below |
| >= | Greater than or equal | age >= 21 | age is 21 or above |
The standard "not equal" operator is <> and it works in every engine. Many databases also accept != as a synonym, so status != 'paid' means the same thing. Prefer <> for portable code. A short example that combines several comparisons:
SELECT name, price, stock
FROM products
WHERE price >= 10
AND price < 50
AND stock <> 0;Comparisons also work on text and dates, not just numbers. For text, < and > compare alphabetically, so 'apple' < 'banana' is true. For dates, earlier is less than later, so order_date >= '2026-01-01' keeps this year onward.
Logical operators: AND, OR, NOT
Logical operators combine or invert truth values so you can build compound conditions. There are three, and they are pure keywords rather than symbols.
- AND is true only when both sides are true. Use it to narrow results.
- OR is true when at least one side is true. Use it to widen results.
- NOT flips a condition, turning true into false and false into true.
-- AND narrows: both must hold
SELECT * FROM users WHERE active = 1 AND country = 'US';
-- OR widens: either may hold
SELECT * FROM users WHERE country = 'US' OR country = 'CA';
-- NOT inverts the condition that follows
SELECT * FROM users WHERE NOT active = 1;The following truth table shows exactly how each operator behaves for every combination of two conditions A and B. Memorise the first two columns and the rest follows naturally.
| A | B | A AND B | A OR B | NOT A |
|---|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE | FALSE |
| TRUE | FALSE | FALSE | TRUE | FALSE |
| FALSE | TRUE | FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE | FALSE | TRUE |
When you mix AND and OR in one WHERE clause, order matters a great deal, and that is covered in the precedence section below. For now, the safe habit is to wrap any OR group in parentheses.
Range and set operators: BETWEEN, IN
These two keywords are shorthands. Anything they do could be written with the comparison and logical operators above, but they read far more clearly.
BETWEEN
BETWEEN tests whether a value falls inside a range, and the range is inclusive of both ends. So price BETWEEN 10 AND 50 is exactly the same as price >= 10 AND price <= 50.
-- these two WHERE clauses are identical
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE price >= 10 AND price <= 50;
-- works on dates too
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';IN
IN checks whether a value matches any item in a list. It replaces a long chain of OR conditions with a compact list, and you negate it with NOT IN.
-- clear and short
SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');
-- exactly equivalent, but noisier
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';
-- the list can come from a subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 1);Pattern matching with LIKE
LIKE compares text against a pattern rather than an exact string. It uses two wildcard characters: the percent sign matches any run of characters, including none at all, and the underscore matches exactly one character. Combine them to describe almost any shape of text.
| Pattern | Wildcard used | Matches | Does not match |
|---|---|---|---|
| 'a%' | % | apple, avon, a | banana |
| '%son' | % | Johnson, Watson | Sonata |
| '%test%' | % | latest, testing | tset |
| '_at' | _ | cat, bat, hat | at, chat |
| 'c_t' | _ | cat, cot, cut | cart, ct |
| 'a_c%' | _ and % | abcd, aac, a1c9 | ac, axyc |
Reading the table, remember the key difference: % stands in for zero, one, or many characters, while _ always stands for exactly one. A pattern with no wildcards behaves like plain equality.
-- names that start with Ma
SELECT name FROM customers WHERE name LIKE 'Ma%';
-- email addresses at gmail
SELECT email FROM customers WHERE email LIKE '%@gmail.com';
-- five letter product codes ending in X
SELECT code FROM products WHERE code LIKE '____X';Two practical notes. Whether LIKE is case sensitive depends on the database and its collation: MySQL is usually case insensitive by default, while PostgreSQL is case sensitive and offers ILIKE for a case insensitive match. And a pattern that begins with a wildcard, like '%son', cannot use a normal index, so leading wildcards on large tables can be slow.
NULL, EXISTS, and quantified comparisons
A few operators deal with the awkward cases: missing values, existence checks, and comparisons against a whole set of values at once.
IS NULL
NULL is SQL's marker for "no value" or "unknown". The single most important rule about it is that you cannot test for it with =. A comparison with NULL never returns true, so = NULL silently matches zero rows. You must use the dedicated IS NULL and IS NOT NULL operators.
SELECT * FROM users WHERE middle_name = NULL;
-- returns 0 rows: anything = NULL is never trueSELECT * FROM users WHERE middle_name IS NULL;
-- use IS NULL / IS NOT NULL to test for the markerWatch out: writing = NULL or <> NULL is one of the most common beginner bugs, and it fails quietly with no error. Always reach for IS NULL and IS NOT NULL. There is a deeper look in the guide to SQL NULL values.
EXISTS
EXISTS takes a subquery and returns true if that subquery produces at least one row. It is a fast way to ask "is there a related record" because the engine stops as soon as it finds the first match.
-- customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);ANY and ALL
ANY and ALL let a single comparison run against every value a subquery returns. ANY is true if the comparison holds for at least one value; ALL is true only if it holds for every value.
-- price greater than the cheapest competitor (at least one)
SELECT * FROM products
WHERE price > ANY (SELECT price FROM competitor_prices);
-- price greater than every competitor (the most expensive)
SELECT * FROM products
WHERE price > ALL (SELECT price FROM competitor_prices);A useful mental shortcut: = ANY (...) is the same as IN (...), and <> ALL (...) is the same as NOT IN (...). For most everyday work, IN and EXISTS cover the same ground more readably.
String concatenation
Joining pieces of text together is called concatenation, and unfortunately this is one place the databases disagree on syntax. There are three spellings you will meet.
| Method | Databases | Example |
|---|---|---|
| CONCAT() | MySQL, PostgreSQL, SQL Server, others | CONCAT(first, ' ', last) |
| || | PostgreSQL, Oracle, SQLite, standard SQL | first || ' ' || last |
| + | SQL Server | first + ' ' + last |
-- CONCAT is the most portable choice
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
-- the || operator, ANSI standard, reads cleanly
SELECT first_name || ' ' || last_name AS full_name
FROM employees;The safest default is CONCAT() because it exists in the widest range of engines. Beware that in some databases the || operator and the plus sign behave oddly when a value is NULL, sometimes turning the whole result into NULL. CONCAT() generally treats NULL as an empty string instead, which is usually what you want.
Operator precedence
When several operators appear in one expression, precedence decides which runs first, just like multiplication happening before addition in ordinary maths. Higher rows in the table below are evaluated before lower rows.
| Precedence | Category | Operators |
|---|---|---|
| 1 (highest) | Multiplicative | * / % |
| 2 | Additive and concat | + - || |
| 3 | Comparison | = <> < > <= >= |
| 4 | Pattern and range | BETWEEN LIKE IN IS |
| 5 | Logical NOT | NOT |
| 6 | Logical AND | AND |
| 7 (lowest) | Logical OR | OR |
The precedence rule that catches the most people is that AND binds tighter than OR. In the query below, the author wanted active accounts on either plan, but the database groups the AND first and quietly lets inactive team accounts through.
SELECT * FROM accounts
WHERE active = 1 AND plan = 'pro' OR plan = 'team';
-- reads as: (active = 1 AND plan = 'pro') OR plan = 'team'
-- inactive 'team' accounts sneak inSELECT * FROM accounts
WHERE active = 1 AND (plan = 'pro' OR plan = 'team');Tip: parentheses cost nothing and remove all doubt. Whenever a condition mixes AND with OR, wrap the OR group in parentheses so the query says exactly what you mean. The same idea applies to arithmetic: write (a + b) * c when that is what you intend.
Most used operators at a glance
Not every operator earns its keep equally. In day to day querying, a small core does the vast majority of the work. The chart below is a rough guide to how often a typical beginner reaches for each family, so you know where to spend your practice time first.
The lesson is clear: comparison and logical operators are the bread and butter, so get comfortable with them first. Range, set, and pattern operators come next, and the quantified comparisons are worth knowing but used less often. For a broader walk through the querying keywords that host these operators, see the data query language commands.
Putting it together
Operators are a small vocabulary with an outsized payoff. Compute with the five arithmetic operators, ask questions with the six comparison operators, combine those questions with AND, OR, and NOT, and reach for BETWEEN, IN, and LIKE when they read more clearly than the long form. Remember the two rules that trip up newcomers: use IS NULL rather than = NULL, and parenthesise any mix of AND and OR. Keep the SQL cheat sheet open beside you while these become second nature, and when you are ready to build real fluency, work through the structured SQL beginner course. Operators are also part of the wider standard described in the SQL overview on Wikipedia.