How to do compare/subtract records

Question

Table A having 20 records and table B showing 19 records. How to find that one record is which is missing in table B. How to do compare/subtract records of these two tables; to find that one record. Running query in Apache Superset.

Answer 1

The exact answer depends on which column(s) define whether two records are the same. Assuming you wanted to use some primary key column for the comparison, you could try:

SELECT a.*
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.pk = a.pk);

If you wanted to use more than one column to compare records from the two tables, then you would just add logic to the exists clause, e.g. for three columns:

WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.col1 = a.col1 AND
                                               b.col2 = a.col2 AND
                                               b.col3 = a.col3)