Arithmetic and Comparison Expressions in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Literal Values, Data Types, and Type Casting
What are Arithmetic Operators in SQL?
SQL evaluates arithmetic the same way regular math does: multiplication and division before addition and subtraction.
That might sound obvious, but it's easy to forget when you're writing a multi-step calculation in one expression. 500 + 12 * 9 doesn't give you 4608. It gives you 608, because the multiplication runs first. If you want addition to run first, you need parentheses.
How do parentheses change the order of operations in SQL?
This matters every time you build a formula with more than one operation. Budget calculations, price adjustments, commission breakdowns — any time the order affects the answer, parentheses make your intent explicit:
SELECT (400 + 150) * 3 AS campaign_costAdd the two components first, then multiply the total by three. Without the parentheses, SQL multiplies 150 by 3 and then adds 400 — a completely different number.
What does the % modulo operator do in SQL?
SQL also has a fifth arithmetic operator you may not have encountered: %, called modulo. It returns the remainder after division:
SELECT 275 % 8 AS remaining_items
275 divided by 8 is 34 with 3 left over. The result is 3. Modulo comes up whenever you need to know what's left after dividing a quantity evenly — items remaining after packing into boxes, positions in a sequence, that kind of thing.
One piece from casting to keep in mind here: dividing two integers still truncates the decimal. 1000 / 3 returns 333, not 333.333.... Cast one side to ::numeric when you need the full result: 1000::numeric / 3 returns 333.333....
Why does arithmetic with NULL return NULL?
The one thing that trips people up: NULL in arithmetic.
Any arithmetic involving NULL produces NULL. 5000 * NULL returns NULL, not zero. A missing value in any part of a formula makes the whole result unknown. If a calculation returns unexpected NULLs, a NULL in one of the inputs is usually why.
A calculation uses: 5000 * NULL. What does SQL return?
Practice Arithmetic Operators in SQL
Brightlane's fulfilment team is planning crate assignments for an incoming delivery. Each crate holds 8 items, and a batch of 275 items has just arrived. The team needs to know how many items will be left over after filling complete crates.
Write a query to return the number of leftover items in a single column named remaining_items.
Output:
- A single row with one column,
remaining_items, containing the leftover count.
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Arithmetic Operators practice problems
Write a query to return the number of leftover items in a single column named remaining_items.
Write a query to return the total licensing cost in a single column named total_cost.
Write a query to return the total campaign cost in a single column named campaign_cost.
Write a query to return each warehouse's whole-unit share in a single column named units_per_warehouse. The leftover units are out of scope here.
Write a query to return each employee's precise share in a single column named exact_share.
Write a query to return the total order cost in a single column named total_order_cost.
Write a query to return what the calculation base * multiplier produces in its current state, in a single column named payout.
Write a query to return both values in a single row.
Write a query to return the tax amount owed in a single column named tax_amount.
Start learning to practice all 9 Arithmetic Operators problems, with instant grading and mastery tracking.
Common questions about Arithmetic Operators
What does PostgreSQL do when you divide by zero?
It stops the query with a division by zero error rather than returning null or infinity. When a divisor could legitimately be zero, guard it with NULLIF so the division produces NULL instead of failing the whole statement.
What type do you get when you mix an integer and a decimal?
Numeric. PostgreSQL widens the integer to match rather than narrowing the decimal, so one plus one and a half comes back as two point five and not as two. Mixing types is the usual way to escape integer division.
What does the modulo operator return for negative numbers?
A result that takes its sign from the left operand. Minus seven modulo three gives minus one, while seven modulo minus three gives one. If you need a result that is always positive, adjust it yourself rather than assuming.
Can you use arithmetic inside a WHERE clause?
Yes. A comparison can hold any expression on either side, so filtering on a doubled price or a computed margin is ordinary SQL. Bear in mind that wrapping a column in arithmetic usually stops an index on that column from being used.
