row subquery in mysql with null value?

blackhorse

SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) IN
             (SELECT column1,column2,column3 FROM t2);

But if there is null value, for example, column2 is NULL, I cannot use this row subquery to compare two tables, am I right?

So if there is null, how do you do that? use the left join on ... with nullif function?

Or use the left join with NULL-safe equal to operator?

I guess <=> is more clear.

Are there "null safe" IN () tricky codes? So I can still use row sunsubquery to compare two tables?

Thanks!

johanafm

First off, your query is equal to

SELECT t1.col1, t1.col2, t1.col3
FROM t1
	INNER JOIN t2 ON t1.col1 = t2.col1 AND
			t1.col1 = t2.col2 AND
			t1.col3 = t2.col3

The problem at hand is that NULL = NULL doesn't evaluate to true

SELECT NULL = NULL;	-- null
SELECT 2 = 2;		-- 1
SELECT 1 = 2;		-- 0

So, if I've understood you correctly and you want to evaluate NULL = NULL to be true, this would solve your issue

SELECT t1.*, t2.*
FROM t1
	INNER JOIN t2 ON (t1.col1 = t2.col1 OR (t1.col1 IS NULL AND t2.col1 IS NULL)) AND
			(t1.col2 = t2.col2 OR (t1.col2 IS NULL AND t2.col2 IS NULL)) AND
			(t1.col3 = t2.col3 OR (t1.col3 IS NULL AND t2.col3 IS NULL))

blackhorse

How about just using null safe equals <=>? I am going to give it a try now. But it seems many threads suggesting use nullif ... instead of null safe equals <=>.

To me, null safe equals seems clearer. Any reasons not using null safe equals? performance issue etc.?

johanafm

<=> was an oversight on my part. I had completely forgotten about it. I have no idea wether the parser and optimizer will arrive at the same thing using <=> vs my initial suggestion, but the <=> should at least not be slower.

While you can make nullif work, you would have to

NULLIF(t1.col1, t2.col1) IS NULL

So I'd go with <=> instead.

blackhorse

Thanks!

I think I should stop "simplifying" my questions. 🙂

As a matter of fact, the query I was using is for compare two tables and find the difference. I would have to use "left join". Some like select tb1.id from tb1 left join tb2 ON ... WHERE tb2.id IS NULL.

So if there were no "null value" issues I could use row subquery, someone already said the left join is better performanced. But row subquery "NOT IN" is kind "straightforward and easy to read"

SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) NOT IN
             (SELECT column1,column2,column3 FROM t2);

But due to there are null values, I think I would have to use "left join" approach.

Thanks again.