Search code examples
sqlsnappydata

SnappyData SQL if else

I have two tables that I need to join 

table_A(ID, val), table_B(ID, val)

to get a new table

RESULT(ID, value)

Where the value should be populated like this

Case1: if there is a ID that exists in in both table_A and table_B, value should be 1,

Case2: if the ID only exists in table_A and does not exists in table_B, value should be 0.

CREATE TABLE RESULT AS
SELECT A.ID, {TODO}
FROM table_A A 
   LEFT OUTER JOIN table_B B 
      On A.ID = B.ID

I know I can use left outer join like above, and replace {TODO} with COALESCE(B.ID, 0) to handle the second case, but how to include case 1 as well in just one SQL sentence in SnappyData SQL?

Solution

  • If id exist in A and not exist in B during a left outer join, B's Id will be null

    SELECT A.ID, (CASE WHEN B.ID is null THEN 0 ELSE 1 END) As value
  FROM TableA A left outer join 
       TableB B
 ON A.ID = B.ID