Operators & Expressions

An expression is anything that produces a value: a column, a literal, a calculation, a function call. This lesson collects SQL's operators — arithmetic, text concatenation, comparison and logic — and the rules that decide how they combine.

Arithmetic

SELECT 7 + 2 AS plus, 7 - 2 AS minus, 7 * 2 AS times, 7 / 2 AS divide, 7 % 2 AS remainder;

Look closely at 7 / 2: dividing two integers gives an integer in SQLite, PostgreSQL and SQL Server (the fraction is discarded). MySQL and Oracle return 3.5. To get a decimal result everywhere, make one side a decimal:

SELECT 7 / 2.0 AS decimal_divide, CAST(7 AS REAL) / 2 AS also_decimal;

Concatenating text

SELECT first_name || ' ' || last_name AS full_name, job_title
FROM employees
WHERE department_id = 3;

|| is the standard concatenation operator (SQLite, PostgreSQL, Oracle). MySQL uses CONCAT(a, b, …) (where || means OR by default), and SQL Server uses + or CONCAT. CONCAT is also available in PostgreSQL and recent SQLite, and it treats NULL as an empty string.

SELECT name, 'City: ' || city AS with_pipes, concat('City: ', city) AS with_concat
FROM customers
WHERE id IN (5, 6);

Literals

SELECT 'It''s here' AS quoted, TRUE AS yes, FALSE AS no;

Comparisons produce values too

SELECT name, price, price > 40 AS expensive
FROM products
WHERE category = 'Books';

A comparison is an expression with a true/false result. In SQLite and MySQL you'll see 1 and 0; PostgreSQL shows true and false.

Operator precedence

SELECT 2 + 3 * 4 AS no_parens, (2 + 3) * 4 AS with_parens;

Precedence varies slightly between databases, so use parentheses whenever an expression could be read two ways.

Expressions everywhere

SELECT oi.order_id,
       oi.quantity * oi.unit_price AS line_total
FROM order_items AS oi
WHERE oi.quantity * oi.unit_price > 60
ORDER BY line_total DESC;

Expressions can appear in the select list, WHERE, ORDER BY, GROUP BY, HAVING, join conditions, UPDATE … SET and INSERT values.

Try it yourself

Show each employee's monthly salary to two decimal places, and what percentage of the CEO's salary (150,000) they earn.