Search code examples
sqllogicbelongs-to

SQL 'belong to' logic

So, I have been trying to write a query in SQL but facing an issue. I am trying to write a 'belongs to' kind of condition. What I want to do is if the values being fetched belongs to a column in another table then populate one thing otherwise populate null.
for ex. 

NAME table
ID    NAMES
1      A
2      B
3      C
4      D
5      E

XYZ table
ID
2
4
5

I wrote the query something like this

(CASE WHEN NAME.ID IN (SELECT ID FROM XYZ) THEN NAME.NAMES ELSE NULL END ) AS 'ABC'

This query does run but it has been running for 14 hours (OBVIOUSLY FOR A VERY HUGE AMOUNT OF DATA) and still there is no result. Is there some flaw in this logic or is there some better way it could be done?

I expect a result like this:

ABC
NULL
B
NULL 
D
E
Solution

  • You just need a plain left join here:

    SELECT
    CASE WHEN t2.ID IS NOT NULL THEN t1.NAMES END AS ABC
FROM NAME t1
LEFT JOIN XYZ t2
    ON t1.ID = t2.ID;

    enter image description here

    Demo

    Note that a CASE expressions else condition, if not explicitly specified, defaults to NULL. This behavior works here because you want to render NULL if a given record in the NAME table does not match to any record in the XYZ table.