sql/spark-sql: if statement syntax in a query

Question

I am looking into some existing spark-sql codes, which trying two join to tables as below:

items_t1_t2 as (
    select *,
    if(id_t1 is not Null, True, False) as in_t1,
    if(id_t2 is not Null, True, False) as in_t2
    from item_t2 full join item_t1
    on id_t2 = id_t1)

I am wondering why there is three elements in the if parentheses? What does the if statement mean here and how it works here? Thanks a lot!

Answer 1

The "IF" statement in Spark SQL (and in some other SQL dialects) has three clauses:

IF (condition_to_evaluate, result_if_true, result_if_false)

In this case, for instance, the expression:

IF(id_t1 IS NOT NULL, True, False) AS in_t1

Is logically equivalent to this one:

id_t1 IS NOT NULL AS in_t1

Or, to put it in another way: in_t1 is just a flag saying "if id_t1 is not null" and the same goes for in_t2 and id_t2.