In SQL, what do SELECT 7 / 2 and SELECT -7 / 2 return on an integer-dividing engine, and which common engines differ?
answer
- operand types decide the division
- toward zero, not down
- two common engines return a fraction
- the cast goes before the slash
- 100 * a / b, left to right
basics
~20 sAn integer-dividing engine returns 3 and -3, because the quotient truncates toward zero. PostgreSQL, SQL Server and SQLite divide integers that way; MySQL and Oracle return a fractional result, 3.5 and -3.5, from the same expression.
solid answer
~40 sIn PostgreSQL, SQL Server and SQLite, two integer operands produce an integer, and the fraction is dropped toward zero: `7 / 2` is 3 and `-7 / 2` is -3, not -4. MySQL's `/` returns a decimal (3.5000) and Oracle's returns 3.5, so the same query changes its answer between engines. To keep the fraction, make an operand non-integer before the division runs: `CAST(a AS DECIMAL(12,4)) / b` or `a * 1.0 / b`. `CAST(a / b AS DECIMAL(12,4))` is too late, because the truncation has already happened. Order matters too: for a = 1 and b = 3, `100 * a / b` gives 33 while `a / b * 100` gives 0.
code
sql · 5 linesSELECT 7 / 2; -- 3
SELECT (-7) / 2; -- -3
SELECT 7.0 / 2; -- 3.5000000000000000
SELECT CAST(7 AS DECIMAL(12,4)) / 2; -- 3.5000000000000000
SELECT CAST(7 / 2 AS DECIMAL(12,4)); -- 3.0000go deeper
Recall that two integers divided on many engines give an integer, so 7 / 2 is 3. Know the fix of making one operand a decimal before the division.
State the truncation direction with a negative example, name which engines divide integers and which do not, and explain why a cast around the quotient arrives too late.
Expect this when a query moves between engines, because nothing errors and only the numbers change. Check operand types first when a ratio looks wrong, and review percentage expressions for the position of the 100.
If several engines run shared SQL, make the exact-numerator form the written convention for ratios, with a test case that would return 0 under integer division, rather than relying on each author remembering the trap.
## What "divides integers" means A division's behaviour depends on the types of its operands, not on the column's meaning or on what the result will be used for. In an engine that divides integers, when both operands of `/` are integer types the result is an integer type too, so there is nowhere to keep a fraction. The fraction is discarded, and the discarding moves **toward zero**. PostgreSQL's function reference says exactly this for integral types and lists `5 / 2` as 2 and `(-5) / 2` as -2. That direction is the part people get wrong on a negative value. `-7 / 2` is -3 rather than -4, because this is truncation and not `FLOOR`. ## Which engines do it | Engine | `7 / 2` | `-7 / 2` | Integer quotient on demand | |---|---|---|---| | PostgreSQL | 3 | -3 | `/` on integers, or `div(7, 2)` | | SQL Server | 3 | -3 | `/` on integers | | SQLite | 3 | -3 | `/` on integers | | MySQL | 3.5000 | -3.5000 | `7 DIV 2` | | Oracle | 3.5 | -3.5 | `TRUNC(7 / 2)` | Oracle's query also needs a `FROM dual` on the versions that require a `FROM` clause. So "1 / 2 is 0" is a statement about particular engines, not about SQL. A query moved from MySQL to PostgreSQL can start returning 0 with no error at all, and one moved the other way can start returning fractions into code that expected whole numbers. ## Keeping the fraction The fix has one rule: **at least one operand must already be non-integer when the division runs**. - `CAST(a AS DECIMAL(12,4)) / b` is explicit, and it runs as decimal division on PostgreSQL, SQL Server, MySQL and Oracle. - `a * 1.0 / b` is the short form. Multiplication runs first, and the decimal literal makes the left side non-integer. - `CAST(a AS FLOAT) / b` also keeps a fraction, but the result is approximate. That is often acceptable for a rate shown on a chart, and it is the wrong habit for an amount that has to reconcile. Three forms look like fixes and are not: 1. `CAST(a / b AS DECIMAL(12,4))` converts a quotient that has already lost its fraction, so it prints 0.0000. 2. `ROUND(a / b, 4)` has the same problem: `ROUND` receives the truncated integer. 3. `a / b * 1.0` multiplies after the division, so it prints 0.0 for a = 1 and b = 2. ## Traps in the fix itself - **Too little room in the cast.** `DECIMAL(5,4)` leaves one digit before the point, so a count of 12 does not fit. PostgreSQL raises an error when the digits left of the point exceed precision minus scale, and SQL Server raises an overflow error too. Size the type for the largest operand. - **SQLite's DECIMAL is not exact.** It has no such storage type, and a cast to `DECIMAL(10,4)` leaves an integer value as an integer, so `CAST(1 AS DECIMAL(10,4)) / 2` still returns 0 there. Use `CAST(a AS REAL) / b` or `a * 1.0 / b`. - **Literals have types too.** `7.0` is an exact decimal on PostgreSQL, MySQL and SQL Server, and `7e0` is a float. `7.0 / 2` is 3.5000000000000000 on PostgreSQL: the extra zeros are the division's result scale, not noise. - **Zero still has to be handled.** Making the numerator exact does nothing for a zero denominator, so a production ratio usually reads `CAST(a AS DECIMAL(12,4)) / NULLIF(b, 0)`. ## Order of evaluation inside the expression `*` and `/` have equal precedence and run left to right, so where the `100` sits changes a percentage. With `a = 1` and `b = 3`, both integers, on an integer-dividing engine: - `a / b * 100` computes 0, then 0. - `100 * a / b` computes 100, then 33, which is correct to a whole percent but loses the rest. - `100.0 * a / b` computes 100.0, then 33.33 and onward. ## Deliberately asking for an integer quotient Sometimes the integer result is the goal, as in computing a page number or a batch number. On an engine where `/` already divides integers, plain `/` is enough, though a comment is kind to the next reader. On MySQL, `DIV` is the integer form, and it truncates toward zero too. On Oracle, wrap the division in `TRUNC`. If you need rounding down rather than toward zero on negative values, use `FLOOR` on a decimal division, because the integer forms all truncate toward zero.
- Why doesn't CAST(a / b AS DECIMAL(12,4)) rescue a ratio that comes back as 0?The expression inside the cast runs first. With two integer operands on an integer-dividing engine, `a / b` has already truncated to 0, and the cast only changes how that 0 is typed, giving 0.0000. Move the cast onto an operand so the division itself is not integer division: `CAST(a AS DECIMAL(12,4)) / b`.
- Why does 100 * a / b return 33 while a / b * 100 returns 0 for integers a = 1 and b = 3?`*` and `/` share a precedence level and run left to right. In the first form 100 * 1 is 100, and 100 / 3 truncates to 33. In the second, 1 / 3 truncates to 0 before the multiplication, and 0 * 100 is 0. Writing `100.0 * a / b` keeps the fraction as well.
- How do you get an integer quotient on MySQL, where / returns a decimal?Use the `DIV` operator: `7 DIV 2` is 3 and `-7 DIV 2` is -3, truncating toward zero just as integer-dividing engines do. PostgreSQL has a similar `div(y, x)` function for numeric values. On Oracle, `/` keeps the fraction, so write `TRUNC(a / b)`.
- Integer division rounds to the nearest whole number, so 7 / 2 gives 4.
- On an integer-dividing engine -7 / 2 floors to -4.
- Every SQL engine returns 0 for 1 / 2.
- Casting the finished quotient to DECIMAL recovers the lost fraction.
- ROUND(a / b, 4) fixes an integer ratio that keeps showing 0.
- Casting an operand to DECIMAL keeps the fraction on every engine.