SQL Operators Reference

SQL operators are the symbols and keywords that let you compare, combine, and calculate values inside a query — everything from a simple = in a WHERE clause to bitwise math and pattern matching with LIKE. Understanding how they work, and especially in what order the database engine evaluates them, is the difference between a query that looks correct and one that actually returns the correct rows. This reference walks through every major operator category, explains the three-valued logic that governs comparisons involving NULL, and shows the precedence rules and mistakes that trip up even experienced developers.

Overview: How Operators Work

Every operator takes one or more values (called operands) and produces a new value. Arithmetic operators produce numbers, comparison operators produce a boolean-like result, and logical operators combine those boolean-like results. SQL operators fall into six broad families:

• Arithmetic — +, -, *, /, % — perform math on numeric columns and literals.
• Comparison — =, != / <>, <, >, <=, >= — compare two values and drive filtering in WHERE/HAVING.
• Logical — AND, OR, NOT — combine multiple conditions.
• Special / membership — BETWEEN, IN, LIKE, IS NULL, EXISTS — concise ways to express common comparison patterns.
• String — || for concatenation (MySQL instead uses the CONCAT() function by default).
• Bitwise — &, |, <<, >>, ~ — operate on the binary representation of integers.

A critical detail beginners miss: SQL uses three-valued logic, not the two-valued true/false logic of most programming languages. Every comparison can evaluate to TRUE, FALSE, or UNKNOWN. Any comparison involving NULL — even NULL = NULL — evaluates to UNKNOWN, and rows whose WHERE condition evaluates to UNKNOWN are excluded from the result, exactly like FALSE. This is why = NULL never matches anything and why IS NULL exists as a dedicated operator.

Under the hood, the query planner evaluates operators as part of building each row’s filter expression during the WHERE (and later HAVING) evaluation step. Equality and range comparisons (=, <, >, BETWEEN) on a raw, un-wrapped column are "sargable" — the optimizer can use an index on that column to jump straight to matching rows. Once you wrap a column in a function or arithmetic (for example WHERE salary * 12 > 100000), the engine generally can no longer use an index on salary and must compute the expression for every row instead.

Syntax

The general shape is always <expression> <operator> <expression>, combined with logical operators when you need more than one condition:

SELECT columns
FROM table
WHERE expression1 operator expression2
  [AND|OR expression3 operator expression4]

Category	Operators	Example
Arithmetic	+ - * / %	price * quantity
Comparison	= != <> < > <= >=	salary >= 50000
Logical	AND OR NOT	active = 1 AND age > 18
Range	BETWEEN ... AND ...	age BETWEEN 18 AND 30
Membership	IN (...)	country IN ('USA','UK')
Pattern match	LIKE	name LIKE 'A%'
Null check	IS NULL / IS NOT NULL	bonus IS NULL
String	||	first || ' ' || last
Bitwise	& | ~ << >>	flags & 4

Examples

Example 1: Comparison operators

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL,
  age INTEGER
);
INSERT INTO staff (id, name, department, salary, age) VALUES
  (1, 'Alice', 'Engineering', 95000, 34),
  (2, 'Bob', 'Sales', 62000, 29),
  (3, 'Carol', 'Engineering', 88000, 41),
  (4, 'David', 'Marketing', 54000, 25);

SELECT name, salary
FROM staff
WHERE salary >= 60000 AND salary <= 90000;

Result:

name	salary
Bob	62000
Carol	88000

The two comparison operators >= and <= form a range check, combined with AND. Alice (95000) is excluded because she is above the upper bound, and David (54000) is excluded because he is below the lower bound. This same range could be written more concisely as salary BETWEEN 60000 AND 90000.

Example 2: Logical operators with parentheses

SELECT name, department, age
FROM staff
WHERE department = 'Engineering' AND (age < 30 OR salary > 90000);

Result:

name	department	age
Alice	Engineering	34

Alice qualifies because she is in Engineering and her salary (95000) exceeds 90000, even though she is not under 30. Carol is in Engineering but is 41 years old and earns 88000, so neither part of the parenthesized OR is true, and she is excluded. Note the parentheses: without them, AND would bind to age < 30 alone before combining with OR, changing the result entirely (see Common Mistakes below).

Example 3: IN and IS NOT NULL together

CREATE TABLE staff2 (
  id INTEGER,
  name TEXT,
  department TEXT,
  bonus REAL
);
INSERT INTO staff2 (id, name, department, bonus) VALUES
  (1, 'Alice', 'Engineering', 5000),
  (2, 'Bob', 'Sales', NULL),
  (3, 'Carol', 'Engineering', 3000),
  (4, 'David', 'Marketing', NULL);

SELECT name, department, bonus
FROM staff2
WHERE department IN ('Engineering', 'Marketing') AND bonus IS NOT NULL;

Result:

name	department	bonus
Alice	Engineering	5000
Carol	Engineering	3000

IN ('Engineering', 'Marketing') is shorthand for department = 'Engineering' OR department = 'Marketing'. David is in Marketing but has a NULL bonus, so bonus IS NOT NULL filters him out; Bob is excluded because Sales is not in the list at all.

Under the Hood: Precedence and Evaluation Order

When an expression mixes several operators, SQL evaluates them according to a fixed precedence, from highest to lowest:

1. Unary +, -, and bitwise ~
2. *, /, %
3. Binary +, -, and string ||
4. Bitwise <<, >>, &, |
5. Comparison operators: =, <, >, <=, >=, <>, IN, LIKE, BETWEEN
6. NOT
7. AND
8. OR (lowest)

Because AND binds tighter than OR, a condition like a OR b AND c is always evaluated as a OR (b AND c), never (a OR b) AND c. Parentheses are the only reliable way to force a different order, and using them even when not strictly required makes intent explicit to future readers. The same left-to-right, precedence-driven evaluation applies to arithmetic and bitwise expressions:

SELECT 10 + 3 AS add_result,
       10 - 3 AS sub_result,
       10 * 3 AS mul_result,
       10 / 3 AS int_div_result,
       10.0 / 3 AS float_div_result,
       10 % 3 AS mod_result;

Result: add_result = 13, sub_result = 7, mul_result = 30, int_div_result = 3, float_div_result ≈ 3.33333333333333, mod_result = 1. Notice that 10 / 3 performs integer division and truncates to 3 because both operands are integers, while 10.0 / 3 uses floating-point division because one operand is a decimal literal — a common source of bugs when computing averages or ratios.

Bitwise operators work on the binary form of integers and are mostly used for flag fields or low-level data:

SELECT 6 & 3 AS bitwise_and,
       6 | 3 AS bitwise_or,
       6 << 1 AS left_shift,
       ~6 AS bitwise_not;

Result: bitwise_and = 2, bitwise_or = 7, left_shift = 12, bitwise_not = -7. String concatenation with || and string comparison follow the same evaluation model:

SELECT 'Hello' || ' ' || 'World' AS greeting,
       ('apple' < 'banana') AS apple_lt_banana;

Result: greeting = ‘Hello World’, apple_lt_banana = 1 (true), since string comparison is lexicographic. In MySQL and SQL Server, || is not concatenation by default (MySQL uses CONCAT(); SQL Server uses + for strings), so check your dialect before relying on it.

Common Mistakes

Mistake 1: Using = NULL instead of IS NULL

CREATE TABLE tickets (id INTEGER, assignee TEXT);
INSERT INTO tickets (id, assignee) VALUES (1, 'Alice'), (2, NULL), (3, 'Bob');

SELECT * FROM tickets WHERE assignee = NULL;

Result: zero rows. This query runs without error but silently returns nothing, because NULL = NULL evaluates to UNKNOWN, not TRUE, under three-valued logic. Fix it with the dedicated null-check operator:

SELECT * FROM tickets WHERE assignee IS NULL;

Result: one row — id = 2, assignee = NULL.

Mistake 2: Forgetting operator precedence between AND and OR

CREATE TABLE leads (id INTEGER, source TEXT, score INTEGER);
INSERT INTO leads (id, source, score) VALUES
  (1, 'web', 80), (2, 'referral', 40), (3, 'web', 30), (4, 'ads', 90);

SELECT * FROM leads WHERE source = 'web' OR source = 'referral' AND score > 50;

Result: id 1 (web, 80) and id 3 (web, 30). The intent was "web or referral leads with a score above 50," but because AND binds tighter than OR, this is parsed as source = 'web' OR (source = 'referral' AND score > 50), so every web lead is included regardless of score. The fix is to make the grouping explicit:

SELECT * FROM leads WHERE (source = 'web' OR source = 'referral') AND score > 50;

Result: only id 1 (web, 80). Now both the web/referral check and the score check apply together, as intended.

Mistake 3: Typing JavaScript-style operators like !==

SELECT * FROM employees WHERE salary !== 50000;

This is not valid SQL and raises a syntax error — SQL only recognizes != or <> for "not equal," never the triple-equals style from JavaScript. Developers coming from JS or comparing objects with ===/!== frequently type this by habit.

Best Practices

• Always use IS NULL / IS NOT NULL to test for missing values — never = NULL or != NULL.
• Add parentheses around mixed AND/OR conditions even when precedence would technically give the right answer; it removes ambiguity for the next reader.
• Prefer BETWEEN and IN over long chains of ORed comparisons — they are shorter and usually just as index-friendly.
• Avoid wrapping indexed columns in functions or arithmetic on the left side of a comparison (e.g. WHERE salary * 1.1 > 100000); rewrite so the raw column stands alone (WHERE salary > 100000 / 1.1) to keep the query sargable.
• Watch out for integer division when both operands are whole numbers; cast or multiply by 1.0 when you need a fractional result.
• Use <> or != consistently within a codebase; both mean "not equal," but mixing them adds noise.
• Remember that || concatenates in SQLite, Oracle, and PostgreSQL, but not in MySQL or SQL Server by default — check the target dialect.

Practice Exercises

• Using the staff table from Example 1, write a query that returns employees who are not in the Engineering department and earn more than 55000. (Hint: combine NOT or <> with a comparison using AND.)
• Using the staff2 table from Example 3, write a query that returns employees whose bonus is either NULL or less than 4000. Expected result: Bob, David (both NULL), and Carol (3000).
• Write a single SELECT with no FROM clause that uses the modulo operator % to determine whether the number 17 is odd or even, returning a column named remainder. Expected result: remainder = 1.

Summary

• SQL operators fall into six families: arithmetic, comparison, logical, membership/range, string, and bitwise.
• SQL uses three-valued logic — any comparison touching NULL evaluates to UNKNOWN, which behaves like FALSE in a WHERE clause.
• Always test for missing data with IS NULL / IS NOT NULL, never with = or !=.
• AND has higher precedence than OR; use parentheses to make grouping explicit and avoid subtle logic bugs.
• Integer division truncates; use a decimal literal or explicit cast when you need a fractional result.
• Wrapping an indexed column in a function or arithmetic can prevent the optimizer from using an index — keep comparisons sargable when possible.
• String concatenation and comparison syntax (|| vs CONCAT()) differ across database systems, so verify behavior in your target dialect.