Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT price * quantity AS line_total FROM order_items;

Try it

SQL Calculated columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Integer division, remainder, precedence and NULL

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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)
SQL
-- 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 / 2 is 3 in SQLite, PostgreSQL and SQL Server. Multiply by 1.0 or CAST one side to get a fractional result: 7 * 1.0 / 2.
  • NULL is contagious. price + NULL is NULL. Wrap nullable columns in COALESCE() when NULL should count as zero.
  • Division by zero is an error in most databases but NULL in SQLite. Guard with NULLIF(): total / NULLIF(count, 0).
  • Money: floating-point types cannot represent many decimal fractions exactly. Store money as integers (cents) or DECIMAL where the database supports it. See DECIMAL and NUMERIC.
  • + is not string concatenation in standard SQL (use ||), except in SQL Server, where + joins strings. In SQLite, '3' + 4 converts the text and returns 7.
  • Dates: date arithmetic differs widely. See date modifiers.
  • Indexes: WHERE price * 2 > 100 cannot use an index on price. Rewrite as price > 50. See sargable predicates.
  • Rounding is a separate step: ROUND(), CEIL() and FLOOR().