Subtract count from left join and inner join in SQL

Question

I want to merge two tables and subtract the number of orders in resulting table from left join from the resulting table in inner join. This is what I have done but I do not know if this is correct or how to proceed?

WITH t1 AS (
    SELECT *
    FROM orders
    LEFT JOIN food ON orders.id = food.id
),
t2 AS (
    SELECT *
    FROM orders
    INNER JOIN food ON orders.id = food.id
)

SELECT
/* now what? */   

Now I want to subtract number of rows in two tables.

I do not know how to proceed, should I join tables again and do subtraction, is there an easier way?

Answer 1

You don't even need two separate queries to answer your question. The only difference in result set between the left and inner joins is that the former will retain order records which do not map. So, you may simply write:

-- query #3

SELECT *
FROM orders o
LEFT JOIN food f
    ON f.id = o.id
WHERE f.id IS NULL;

If you combine the result set from above with the inner join query #2, you will get the total left join query #1 result set without the WHERE clause.

So count(query #1) = count(query #2) + count(query #3)