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.

Answer 1

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.