SQL command to compute table difference for two column logical AND

Question

I have table1

+------+----------------+
| ppl  | action         |
+------+----------------+
| p2   | Run            |
| p1   | Jump           |
| p3   | Walk           |
+------+----------------+

and table2

+------+----------------+
| ppl  | action         |
+------+----------------+
| p1   | Eat            |
| p2   | Look           |
| p3   | Walk           |
+------+----------------+

First I would like to get two tables: difference. The difference table will be any ppl and action pair in table2 but NOT in table 1.

+------+----------------+
| ppl  | action         |
+------+----------------+
| p1   | Eat            |
| p2   | Look           |
+------+----------------+

What are the sql commands to achieve there? Some people point me to the previous post: Return row only if value doesn't exist

Unfortunately, this post only discuss filtering based on one column. I struggled to generalize it for two columns since the where clause does not take two columns at the same time. So please bare with me and walk me through the filtering on a two column logical AND solution.

Thanks!

You'll want an SQL join, and one of my favorite ways to explain that is [CodingHorror's blog post about it](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/). In this case, you'd be looking for a `LEFT OUTER JOIN` from table2 to table1, where `table1.some_field IS NULL` — Rogue, Jan 06 '22 at 02:15

score 1 · Accepted Answer · answered Jan 06 '22 at 02:19

You could use exists logic here:

SELECT t2.ppl, t2.action
FROM table2 t2
WHERE NOT EXISTS (SELECT 1 FROM table1 t1
                  WHERE t1.ppl = t2.ppl AND t1.action = t2.action);

A left anti-join is another option:

SELECT t2.ppl, t2.action
FROM table2 t2
LEFT JOIN table1 t1
    ON t1.ppl = t2.ppl AND t1.action = t2.action
WHERE t1.ppl IS NULL;

SQL command to compute table difference for two column logical AND

1 Answers1