SQL Reference ยท Operators and Predicates
SQL arithmetic operators
Reference for SQL arithmetic operators + - * / and %: operator precedence, integer division, division by zero, NULL propagation and dialect differences.
SQL has the usual arithmetic operators: + addition, - subtraction and negation, * multiplication, / division and % remainder (modulo). They work on numeric columns and expressions anywhere a value is allowed: in SELECT, WHERE, ORDER BY, UPDATE ... SET and so on.
Two things surprise newcomers. Dividing two integers can give an integer in many databases, and any arithmetic with NULL gives NULL.
SELECT price * quantity AS line_total FROM order_items;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
a + b a - b a * b a / b a % b -a +a
| Operator | Meaning | Precedence |
|---|---|---|
-a, +a |
Sign | Highest |
*, /, % |
Multiply, divide, remainder | Next |
+, - |
Add, subtract | Lowest of the arithmetic operators |
Operators of equal precedence are evaluated left to right. Use parentheses to make the intent clear.
Returns: a number. The type follows the operands: integer with integer gives integer for +, -, *; if either side is real or decimal, the result is real or decimal.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
7 / 2 |
3 (integer division) |
3 |
3.5000 (/ is exact, DIV is integer) |
3 |
3.5 |
| Division by zero | NULL |
Error | NULL (or error in strict mode for writes) |
Error | Error |
% operator |
Yes | Yes | Yes (also MOD) |
Yes | No: use MOD(a, b) |
Sign of -7 % 3 |
-1 |
-1 |
-1 |
-1 |
-1 from MOD(-7, 3) |
| Integer overflow | Switches to real | Error | Error | Error | Not applicable (numbers are decimal) |
-- PostgreSQL: force decimal division SELECT 7::numeric / 2; -- 3.5000000000000000 -- MySQL: integer division operator SELECT 7 DIV 2; -- 3 -- Oracle SELECT MOD(7, 3) FROM dual; -- 1
Usage notes
- Integer division truncates.
7 / 2is3in SQLite, PostgreSQL and SQL Server. Multiply by1.0orCASTone side to get a fractional result:7 * 1.0 / 2. NULLis contagious.price + NULLisNULL. Wrap nullable columns inCOALESCE()whenNULLshould count as zero.- Division by zero is an error in most databases but
NULLin SQLite. Guard withNULLIF():total / NULLIF(count, 0). - Money: floating-point types cannot represent many decimal fractions exactly. Store money as integers (cents) or
DECIMALwhere the database supports it. SeeDECIMAL and NUMERIC. +is not string concatenation in standard SQL (use||), except in SQL Server, where+joins strings. In SQLite,'3' + 4converts the text and returns7.- Dates: date arithmetic differs widely. See date modifiers.
- Indexes:
WHERE price * 2 > 100cannot use an index onprice. Rewrite asprice > 50. See sargable predicates. - Rounding is a separate step:
ROUND(),CEIL() and FLOOR().
Related
MOD(),ABS(),ROUND(),CAST,NULL and three-valued logic- Lessons: Operators, Calculated columns