Suppose I have some table named tableA and column1 in tableA has some null values and strings.

Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column contains null values and strings as well.

I have a statement similar to the following:

select *
from tableA
where tableA.column1 in (select * from tempTable);

However, I want it to also select the contents when tableA.column1 is null. The in operator ignores that case.

Is there a different operator that considers null?

Or is there anyway I can apply certain constraints in the where clause under true or false conditions.

Like for example in the incorrect statement below:

select *
from tableA
where (if condition1, then tableA.column1 =.... elseif condition2 then tableA.column1 =....);

2 Answers 2

SELECT tableA.*
FROM tableA
       FROM tempTable ) temp ON tableA.column1 <=> temp.column1

<=> is null-safe compare operator. It assumes that NULL is equal to NULL whereas regular comparing operator = considers them unequal.

Subquery is used for to collapse equal values in tempTable - if not then each value from tableA may be selected a lot of times.


If you want al NULL from column1, simply add a OR clöause that check if ULL

select *
from tableA
where tableA.column1 in (select column1 from tempTable) OR tableA.column1 IS NULL;
  • If tableA contains NULLs whereas tempTable does not then this query will give wrong result.
    – Akina
    Nov 11, 2020 at 12:38
  • @Akina As i understand the wuery, he will all rows that also are in the temptable and all NULL Rows from TableA, so i think that is a correct answer. I am not completely sure, but after work i will test the theory, your query should produce a cross join, because many NULL from tablea will be joined with NULL Values from temptable. So my query should yield the correct result.
    – nbk
    Nov 11, 2020 at 12:58
  • As i understand the wuery, he will all rows that also are in the temptable and all NULL I see. If the rows with NULLs must be returned unconditionally then your query is correct.
    – Akina
    Nov 11, 2020 at 13:09
  • Sorry, maybe I should of been clear. tempTable may or may not contain null. So @Akina's solution is what I needed.
    – Tokzeras
    Nov 11, 2020 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.