Anti Join based on multiple keys / columns (SQL)

Question

The setting is simple, I wanted to retrieve all rows from table A that were not present in table B. Because a unique row can be identified using 4 columns, I needed to have a way to write the WHERE statement that it works correctly.

My solution is to concatenate the 4 columns and use that as "one" column/key to do the outer join:

select      * 
from        table_A
where       filter_condition = 0
and         (column1 || column2 || column3 || column4) not in (
            select          A.column1 || A.column2 || A.column3 || A.column4
            from            table_A A -- 1618727
            inner join      table_B B
            on              A.column1 = B.column1 
            and             A.column2 = B.column2 
            and             A.column3 = B.column3 
            and             A.column4 = B.column4
            and             filter_condition = 0
            )

My question is, is this a good way of doing this or am I doing something fundamentally wrong?

To be clear, the desired result is simply to get back only the rows of table_A that I "lose" due to the INNER JOIN with table_A and table_B.

Note that `'AB' || 'C' = 'A' || 'BC'`, but `('AB', 'C') <> ('A', 'BC')`. — jarlh, Oct 24 '19 at 08:50
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Oct 24 '19 at 08:52
Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, Oct 25 '19 at 00:55
@a_horse_with_no_name This is a duplicate, why did you reopen it? The asker in the post: 'To be clear, the desired result is simply to get back only the rows of table_A that I "lose" due to the INNER JOIN with table_A and table_B.' The asker in a comment: 'Marked my question as duplicate, sorry for the inconvenience.' (From revisions: 'Post Closed as "duplicate" by philipxy, jarlh, Community♦') — philipxy, Oct 25 '19 at 01:07

score 4 · Accepted Answer · answered Oct 24 '19 at 08:39

4

You seem to be looking for not exists:

select a.* 
from table_a a
where a.filter_condition = 0
and not exists (
    select 1
    from table_b b
    where 
        a.column1 = b.column1 
        and a.column2 = b.column2 
        and a.column3 = b.column3 
        and a.column4 = b.column4
)

This will give you all records in table_a that do not have a corresponding record in table_b.

answered Oct 24 '19 at 08:39

GMB

216,147
25
84
135

This seems the safest and best solution, as it returns the two rows I am looking for (and not a lot of others as well). Marked my question as duplicate, sorry for the inconvenience. – Anonymous Oct 24 '19 at 09:17

score 2 · Answer 2 · answered Oct 24 '19 at 08:41

Using a LEFT JOIN between A and B and checking for a NULL row in B is probably easier:

SELECT *
FROM table_A A
LEFT JOIN table_B B ON A.column1 = B.column1 
                   AND A.column2 = B.column2 
                   AND A.column3 = B.column3 
                   AND A.column4 = B.column4
WHERE B.column1 IS NULL
  AND A.filter_condition = 0

score 2 · Answer 3 · answered Oct 24 '19 at 08:58

2

You should be able to use tuples (aka row constructors) in PostgreSQL:

select * 
from table_a
where filter_condition = 0
and (column1, column2, column3, column4) not in
(
  select column1, column2, column3, column4
  from table_b
);

If the columns can be null, then better use NOT EXISTS, as null=null results in "unknown" rather than in true or false.

answered Oct 24 '19 at 08:58

Thorsten Kettner

89,309
7
49
73

The tuple comparison properly deals with null values: https://rextester.com/XDWI48444 – Oct 24 '19 at 09:40
@a_horse_with_no_name: Ah, great. Thank you for checking this. So, this would only be a problem when *all* these columns were null in a table_b row. – Thorsten Kettner Oct 24 '19 at 09:49
Correct, in that case we are back to the old "NOT IN problem" with null values. – Oct 24 '19 at 09:51

Anti Join based on multiple keys / columns (SQL)

3 Answers3