How to subtract 2 columns without condensing the table

Question

So I have 2 tables One is a supply table and the other is a table of all checkouts of specific models:

Table 1 Checkouts

Model	Checkout quantity
JD33	5

Table 2 Supply racks

Model	Total quantity
JD33	90
ABC1	75

What I want to do is subtract checkout quantity from total quantity. So essentially what I'm getting is this:

Table 3

Model	Total quantity
JD33	85

I am only getting back models that are in my checkout list. If it is not in the checkout list I want the total quantity to stay the same and still be visible within the table.

Here is my current query:

select SILVER_RACKS.QTY as QTY,
    SILVER_RACKS.MODEL as MODEL,
    SILVER_RACKS.QTY-CHECKOUT_TOTAL.CHECKOUT_QTY  as total_QTY 
 from SILVER_RACKS SILVER_RACKS,
    CHECKOUT_TOTAL CHECKOUT_TOTAL 
 where SILVER_RACKS.MODEL=CHECKOUT_TOTAL.MODEL

Which is returning as described above.

Select from supply and left join to checkout using modern join logic (not that old format you have above). — Isolated, Jun 27 '23 at 19:57
There's no need to provide aliases for columns or tables when they're the same as the actual name. Table aliases are usually short, so you don't have to write the long table name everywhere. — Barmar, Jun 27 '23 at 20:03
[Explicit JOINs vs Implicit joins](https://stackoverflow.com/a/53061589/11865956) — BeRT2me, Jun 27 '23 at 20:04
Thanks for the help guys rarely use the site so I'm not sure on all the practices sorry. And I will be using more explicit joins in the future I was tasked with updating some guys old project and had stumbled upon this issue and was unaware to change. — JaredCusick, Jun 27 '23 at 20:09

Barmar · Accepted Answer · 2023-06-27T20:02:51.027

2

Use LEFT JOIN to get rows in the result that don't have any match. Use COALESCE() to provide a default 0 value for checkout_qty when there's no match.

SELECT r.qty, r.model, r.qty - COALESCE(c.checkout_qty, 0) AS total_qty
FROM silver_racks AS r
LEFT JOIN checkout_total AS c ON r.model = c.model

edited Jun 27 '23 at 20:02

answered Jun 27 '23 at 19:59

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered Jun 27 '23 at 20:00

You need to use a left join:

select SILVER_RACKS.QTY as QTY,
    SILVER_RACKS.MODEL as MODEL,
    SILVER_RACKS.QTY - COALESCE(CHECKOUT_TOTAL.CHECKOUT_QTY,0) as total_QTY 
 from SILVER_RACKS LEFT JOIN CHECKOUT_TOTAL ON SILVER_RACKS.MODEL=CHECKOUT_TOTAL.MODEL;

Left join will return everything from the left table and only the matching rows from the right table. If there is no matching row, CHECKOUT_QTY is null. By using COALESCE, we substitute zero on such rows, ensuring that the total_QTY is calculated on each row of the output.

As an aside, generally I would advise for inner joins to explicitly state inner join and to not use the where clause to specify the join predicates.

How to subtract 2 columns without condensing the table

2 Answers2