(Beginner) MYSQL: What can I do about this where statement?

Question

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 =....);

Answer 1

SELECT tableA.*
FROM tableA
JOIN ( SELECT DISTINCT column1
       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.

Answer 2

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;