Search code examples
sqldashdb

SQL how to check is a value in a col is NOT in another table

Maybe I need another coffee because this seems so simple yet I cannot get my head around it.

Let's say I have a tableA with a col1 where employee IDs are stored.... ALL employee IDs. And the 2nd table, tableB has col2 which lists all employeeID who have a negative evaluation.

I need a query which returns all ID's from col1 from table1 and a newcol which show a '1' for those ID's which do NOT exist in col2 of TableB.

I am doing this in dashDB

Solution

  • One option uses a LEFT JOIN between the two tables:

    SELECT a.col1,
       CASE WHEN b.col2 IS NULL THEN 1 ELSE 0 END AS new_col
FROM tableA a
LEFT JOIN tableB b
    ON a.col1 = b.col2