SQL - Operators

SQL Operators

What is an Operator in SQL?

An SQL operator is a special character or keyword used in a WHERE clause or HAVING clause to perform an operation. These operations can be arithmetic calculations, comparisons between values, or logical combinations of multiple conditions. They are the fundamental building blocks for filtering, joining, and manipulating data.

For example, in select * from products where price > 100;, the greater than symbol (>) is an operator that filters for products with a price higher than 100.

Types of SQL Operators

SQL operators can be grouped into three main categories:

1. SQL Arithmetic Operators

These operators perform mathematical calculations on numeric data.

Operator	Description	Example
+	Addition	SELECT price + 5.00; (Adds 5 to the price)
-	Subtraction	SELECT quantity - 1; (Decrements quantity)
*	Multiplication	SELECT price * quantity; (Calculates total cost)
/	Division	SELECT total_sales / 12; (Calculates monthly average)
%	Modulus (Remainder)	SELECT id % 2; (Returns 0 for even IDs, 1 for odd IDs)

2. SQL Comparison Operators

These operators compare two expressions and return a boolean value: TRUE, FALSE, or UNKNOWN (if one operand is NULL). They are the core of any WHERE clause.

Operator	Description	Example
=	Equal to	WHERE status = 'shipped'
!=	<>	Not equal to
>	Greater than	WHERE stock_level > 0
<	Less than	WHERE price < 50.00
>=	Greater than or equal to	WHERE score >= 90
<=	Less than or equal to	WHERE discount <= 0.25

3. SQL Logical Operators

These operators combine the results of other conditions to create more complex filters. They also handle special cases like ranges, lists, and pattern matching.

Operator	Description	Example
AND	Returns TRUE if all conditions are TRUE.	WHERE price > 50 AND category = 'Electronics'
OR	Returns TRUE if any condition is TRUE.	WHERE status = 'shipped' OR status = 'delivered'
NOT	Reverses the result of a condition.	WHERE NOT country = 'Canada'
IN	Returns TRUE if a value matches any value in a list.	WHERE category IN ('Books', 'Movies', 'Music')
BETWEEN	Returns TRUE if a value is within a specified range (inclusive).	WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31'
LIKE	Performs pattern matching on strings using wildcards (% for any sequence of characters, _ for a single character).	WHERE email LIKE '%@gmail.com'
IS NULL	Returns TRUE if a value is NULL.	WHERE shipping_address IS NULL
IS NOT NULL	Returns TRUE if a value is not NULL.	WHERE completed_at IS NOT NULL
ANY / SOME	Compares a value to each value in a list or subquery and returns TRUE if any comparison is TRUE. (Less common)	WHERE price > ANY (SELECT min_price FROM competitor_prices)
ALL	Compares a value to all values in a list or subquery and returns TRUE if all comparisons are TRUE. (Less common)	WHERE price > ALL (SELECT max_price FROM budget_products)
EXISTS	Returns TRUE if a subquery returns one or more rows.	WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id)

SQL Operator Precedence

Operator precedence determines the order in which operators are evaluated in a complex expression. Higher precedence operators are evaluated first. You can always use parentheses () to override the default order and ensure clarity.

Precedence Level	Operators
1 (Highest)	*, /, %
2	+, -
3	All comparison operators (=, >, <, etc.)
4	NOT
5	AND
6 (Lowest)	OR

Example of Precedence

Consider the expression:

select * from products
where category = 'Audio' or category = 'Video' and price < 100;

Because AND has higher precedence than OR, this query is interpreted as: “Find products where the category is ‘Video’ AND the price is less than 100, OR products where the category is ‘Audio’ (at any price).” This is likely not what was intended.

To get the intended result (“Find products where the price is less than 100 and the category is either ‘Audio’ or ‘Video’”), you must use parentheses:

select * from products
where (category = 'Audio' or category = 'Video') and price < 100;

Common Pitfalls and Best Practices

• Always Use Parentheses: When mixing AND and OR in a WHERE clause, always use parentheses to make your logic explicit and avoid precedence errors.
• NULL is Special: NULL does not equal NULL. You cannot use WHERE my_column = NULL. You must always use WHERE my_column IS NULL or WHERE my_column IS NOT NULL.
• IN vs. Multiple ORs: For checking against a list of values, WHERE category IN ('A', 'B', 'C') is cleaner and often more performant than WHERE category = 'A' OR category = 'B' OR category = 'C'.
• LIKE Wildcard Performance: A LIKE query starting with a wildcard (e.g., LIKE '%text') is often slow because it cannot use an index effectively. If possible, structure your queries so the wildcard is not at the beginning.