PG SQL case when with multiple conditions - simplier way to write statement?-CodePudding

I'm doing a query like so:

SELECT a.col_1,
       a.col_2,
       a.col_3, 
       a.col_4,
       CASE WHEN b.col_1 is null and b.col_2 is null and b.col_3 is null then 0 else 1 end as fraud
FROM table_a a
LEFT JOIN table_b b
on a.col_1 = b.col_1 and a.col_2 = b.col_2 and a.col_3 = b.col_3

is there a simplier way to write the case when statement here? I don't want to have to write so many conditions. hoping for a better way to streamline this.

CodePudding user response：

You join the tables on equality of the three columns. If one of the columns is null in either table, the row in question wont get joined. This means, if you see a null in any of the table_b columns in your joined rows, then you do this because you are looking at an outer joined row where all b columns are set to null. In other words, if b.col_1 is null, then so are b.col_2, b.col_3 and any other b column.

Hence:

SELECT a.col_1,
       a.col_2,
       a.col_3, 
       a.col_4,
       CASE WHEN b.col_1 is null then 0 else 1 end as fraud
FROM table_a a
LEFT JOIN table_b b on a.col_1 = b.col_1
                   and a.col_2 = b.col_2
                   and a.col_3 = b.col_3;

CodePudding user response：

You can use num_nulls()

num_nulls(col_1, col_2, col_3) > 0 as is_fraud

The above results in a boolean true/false value, not a and integer with 0/1. If you really want an integer instead of a boolean you can still wrap the result of num_nulls() into a CASE expression.