To subtract in SQL, put the minus operator between two numbers or numeric columns in a SELECT statement, for example SELECT price - cost AS profit FROM products;. To remove the rows one query returns from another query’s results, use EXCEPT (Oracle also accepts MINUS). The examples below work in SQL Server and Azure SQL (T-SQL), PostgreSQL, MySQL 8, SQLite 3 and Oracle, with differences called out where the databases disagree.
A few terms first
You only need a database you can run queries against and a table with at least two numeric columns. A few words come up repeatedly:
- Operand: a value on either side of the operator. In
a - b,bis taken away froma, so order matters. - Alias: a name you give a calculated column with
AS, so the result shows up asprofitinstead of an unnamed expression. - Result set: the rows a query returns. Set operators such as
EXCEPTwork on whole result sets rather than single values. - NULL: a missing value. It is not zero, and it changes how subtraction behaves (covered below).
Subtract one column from another
This is the method most people need: a new calculated column, row by row, with nothing in the table changed.
- Open your query editor (SQL Server Management Studio, psql, the MySQL command-line client, the
sqlite3shell or any database tool) and connect to the database that holds your table. - Start a
SELECTand list any columns you want to see as-is, such as an ID or name. - Add the larger value first, a minus sign, then the value to take away:
price - cost. - Name the result with an alias:
price - cost AS profit. - Finish with
FROMand the table name, then run the query. Each row shows its own difference in theprofitcolumn.
SELECT product_name, price, cost, price - cost AS profit
FROM products;
You can also subtract plain numbers with no table at all. SELECT 3 - 5; returns -2 in MySQL, PostgreSQL, SQLite and SQL Server. In SQL Server the operand can be any numeric type except bit, according to the T-SQL subtract operator reference. Use parentheses when you mix operators: (price - cost) * quantity subtracts before it multiplies.
Subtract totals instead of single rows
To get one net figure, subtract one aggregate from another inside the same query. SELECT SUM(amount) - SUM(refund) AS net_sales FROM orders; returns a single number. Add GROUP BY region to get one net figure per region. You can also use a calculation in a filter, such as WHERE price - cost < 5, to find low-margin products.
Subtracting dates depends on the database
Dates are where the dialects split the most. Some let you use the minus sign directly; others want a function, and the argument order is not the same everywhere.
| Database | Days between two dates | Go back 7 days |
|---|---|---|
| SQL Server / Azure SQL | DATEDIFF(day, start_date, end_date) | DATEADD(day, -7, order_date) |
| PostgreSQL | end_date - start_date (returns an integer) | order_date - 7 |
| MySQL | DATEDIFF(end_date, start_date) | DATE_SUB(order_date, INTERVAL 7 DAY) |
| SQLite | julianday(end_date) - julianday(start_date) | date(order_date, '-7 days') |
Watch the order. SQL Server’s DATEDIFF takes the start date first and returns a negative number if the start is later than the end. MySQL’s DATEDIFF works the other way round: it returns the first argument minus the second and ignores the time part. MySQL’s TIMESTAMPDIFF(unit, start, end) goes back to start-first order and lets you choose hours, months or years. In PostgreSQL, subtracting two timestamps gives an interval such as 63 days 15:00:00 rather than a number; the PostgreSQL date/time operators table lists every combination.
SQL Server’s minus sign can take days off a datetime value (order_time - 1.5 moves back 36 hours), but it can’t be used with date, time, datetime2 or datetimeoffset. Use DATEADD with a negative number for those.
Subtract the previous row’s value
A common request is the change from one row to the next, such as today’s sales minus yesterday’s. The LAG window function fetches the value from the preceding row so you can subtract it. It’s available in SQL Server 2012 and later, MySQL 8, PostgreSQL and SQLite 3.25.0 and later.
SELECT sale_date, revenue,
revenue - LAG(revenue) OVER (ORDER BY sale_date) AS change
FROM daily_sales;
| sale_date | revenue | change |
|---|---|---|
| 2026-03-02 | 410 | NULL |
| 2026-03-03 | 455 | 45 |
| 2026-03-04 | 398 | -57 |
The first row has no earlier row, so LAG returns NULL and the change is NULL. You can pass a default as the third argument, LAG(revenue, 1, 0), to get a number instead. The ORDER BY inside OVER is what decides which row counts as “previous”, so never leave it out. Add PARTITION BY store_id to restart the comparison for each store.
Subtract one query’s results from another with EXCEPT
Sometimes “subtract” means “show me what’s in list A but not in list B.” That’s a set operation, not arithmetic. EXCEPT returns the distinct rows from the first query that the second query doesn’t return.
SELECT email FROM newsletter_signups
EXCEPT
SELECT email FROM unsubscribes;
- Both queries must return the same number of columns, in the same order, with compatible data types.
- Order matters: swapping the two queries gives a different answer.
- Duplicates are removed. PostgreSQL, MySQL and Oracle also support
EXCEPT ALLto keep them; SQLite and SQL Server do not. - Oracle’s traditional keyword is
MINUS, and its current documentation listsEXCEPTas a synonym. SQL Server, PostgreSQL, MySQL and SQLite document onlyEXCEPT, so use that keyword there. - MySQL added
EXCEPTin 8.0.31, as the MySQL EXCEPT clause page notes. On older versions, useLEFT JOINand keep the rows where the right-hand sideIS NULL, or useNOT EXISTS.
Worked example: stock left on the shelf
Here’s a small, made-up stockroom table. Alex, Jordan and Priya each counted one item, and one sold figure hasn’t been entered yet.
CREATE TABLE stockroom (item VARCHAR(30), counted_by VARCHAR(20), received INT, sold INT);
INSERT INTO stockroom VALUES
('Mugs', 'Alex', 80, 52),
('T-shirts', 'Jordan', 150, NULL),
('Tote bags', 'Priya', 60, 61);
SELECT item, counted_by, received, sold,
received - sold AS left_raw,
received - COALESCE(sold, 0) AS left_on_shelf
FROM stockroom;
| item | counted_by | received | sold | left_raw | left_on_shelf |
|---|---|---|---|---|---|
| Mugs | Alex | 80 | 52 | 28 | 28 |
| T-shirts | Jordan | 150 | NULL | NULL | 150 |
| Tote bags | Priya | 60 | 61 | -1 | -1 |
Two things stand out. The T-shirts row comes back NULL in left_raw because one operand is missing, and COALESCE(sold, 0) treats the missing figure as zero. The tote bags show -1, which is mathematically correct and probably a counting mistake worth checking. A negative result is a data signal, not a SQL error.
When the answer looks wrong
- The result is NULL. Any subtraction with a NULL operand gives NULL. Wrap the column in
COALESCE(column, 0), which works in all five databases (SQL Server also hasISNULL). Only do this when a missing value really means zero. - MySQL error 1690, “BIGINT UNSIGNED value is out of range”. When one operand is an
UNSIGNEDinteger, MySQL produces an unsigned result, so a negative answer fails. Cast the columns toSIGNEDor enable theNO_UNSIGNED_SUBTRACTIONSQL mode. - Decimals disappear. Subtraction keeps the operands’ type, so integers stay integers. The surprise usually comes when a subtraction feeds a division: in PostgreSQL, for example,
(10 - 5) / 2returns2because integer division drops the remainder. Write2.0or cast a column toDECIMALto keep the fraction. - Numbers stored as text. Depending on the database, you get a type or conversion error, or the text is converted quietly and may produce a value you didn’t expect. Fix the column type, or convert it in the query with
CAST(column AS DECIMAL(10,2)). - A date difference is off by one. SQL Server’s
DATEDIFFcounts boundaries crossed, so 11:59 PM to 12:00 AM the next day counts as one day. MySQL’sDATEDIFFignores times completely. - Two minus signs in a row.
--starts a comment in SQL (MySQL treats it as a comment only when a space or tab follows). Writeprice - -5with a space, or better,price + 5.
Check the math before you save it
A SELECT never changes data, so it’s the safe place to test. Spot-check two or three rows by hand, and compare SUM(received) - SUM(sold) with SUM(received - sold): if they differ, some rows contain NULLs.
Warning: an UPDATE such as UPDATE stockroom SET received = received - 5 WHERE item = 'Mugs'; overwrites the stored value, and running it twice subtracts twice. Run the matching SELECT with the same WHERE first, then do the update inside a transaction. In SQL Server that’s BEGIN TRANSACTION;, your UPDATE, a check, then COMMIT; to keep it or ROLLBACK; to undo it. If the change is already committed, the fix is a reverse update (+ 5) or a restore from backup.
Other questions about SQL subtraction
Is there a SUBTRACT function in SQL?
No. Standard SQL uses the - operator. Functions like DATEDIFF and DATE_SUB exist only for dates, and EXCEPT handles row sets.
How do I subtract values from two different tables?
Join the tables on a shared key, then subtract the columns: SELECT b.month, b.amount - s.amount AS remaining FROM budget b JOIN spending s ON s.month = b.month;. Use a LEFT JOIN with COALESCE if some months have no spending row.
How do I get a positive difference no matter the order?
Wrap it in ABS(): ABS(a - b) returns the distance between the two values without a sign.
Can I subtract in SQL the way I would in a spreadsheet?
The idea is the same, but SQL applies one expression to every row at once instead of copying a formula down. If you’re moving a sheet into a database, comparing it with subtracting two columns in Excel helps map each formula to a query.
Start with the plain price - cost query on a table of your own, then add COALESCE once you see a NULL. If your data ends up in application code, the same rules carry over to subtraction in Python and the JavaScript minus operator.