SQL Operators and Expressions

Concept

An expression is anything that produces a value: a literal, a column, a function call, or operators combining them. Expressions can appear in the select list, in WHERE, in ORDER BY, inside functions and in CHECK constraints.

Operator families

Syntax

SELECT first_name,
       salary,
       salary * 12                AS annual,
       salary * 0.10              AS bonus,
       salary + (salary * 0.10)   AS with_bonus
FROM   employees;

Example

-- Precedence: AND binds tighter than OR
SELECT * FROM employees
WHERE  dept_id = 10 OR dept_id = 20 AND salary > 80000;

-- Reads as: dept_id = 10 OR (dept_id = 20 AND salary > 80000)

-- Almost certainly what was meant:
SELECT * FROM employees
WHERE  (dept_id = 10 OR dept_id = 20) AND salary > 80000;

Explanation

The first query returns every employee in department 10 regardless of salary, which is not what the English sentence employees in department 10 or 20 earning over 80000 means. SQL precedence runs: arithmetic, then comparison, then NOT, then AND, then OR. Parentheses cost nothing and remove the ambiguity permanently.

Integer division

-- PostgreSQL, SQL Server, Oracle: integer / integer = integer
SELECT 7 / 2;        -- 3

-- Force a decimal result
SELECT 7 * 1.0 / 2;  -- 3.5
SELECT CAST(7 AS DECIMAL(10,2)) / 2;  -- 3.50

Important rules

• Any arithmetic with NULL yields NULL. salary + bonus is NULL for every row where bonus is NULL.
• Comparison with NULL yields UNKNOWN, never TRUE. Use IS NULL.
• <> is the standard not equal; != is accepted almost everywhere but is not standard.
• Concatenation is the least portable operator in SQL. || is standard, MySQL needs CONCAT() unless PIPES_AS_CONCAT is enabled.

Common mistakes

• Mixing AND and OR without parentheses.
• Expecting 7 / 2 to give 3.5 in a dialect that does integer division.
• Using = NULL instead of IS NULL.
• Wrapping an indexed column in an expression, for example WHERE YEAR(order_date) = 2024, which prevents the index from being used.

Best practices

• Parenthesise every mixed AND/OR condition, even when precedence already agrees with you.
• Keep the indexed column bare on one side of a comparison: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'.
• Name every computed column with a clear alias.

Practice

1. Rewrite WHERE status = 'open' OR status = 'pending' AND total > 1000 so it matches the sentence open or pending orders over 1000.
2. What does SELECT 10 + NULL return, and why?
3. Write an expression that returns each employee's monthly salary rounded to two decimal places.