I'm trying to run a query where two columns are not the same, but it's not returning any results:
SELECT * FROM `my_table` WHERE `column_a` != `column_b`;
column_a AND column_b are of integer type and can contain nulls. I've tried using <> IS NOT, etc without any luck. It's easy to find if they're the same using <=>, but <> and != doesn't return any rows. (using Mysql 5.0).
Thoughts?
Find duplicate values in multiple columns In this case, you can use the following query: SELECT col1, COUNT(col1), col2, COUNT(col2), ... FROM table_name GROUP BY col1, col2, ... HAVING (COUNT(col1) > 1) AND (COUNT(col2) > 1) AND ...
We can use both SQL Not Equal operators <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that '<>' is in line with the ISO standard while '!=
The symbol <> in MySQL is same as not equal to operator (!=). Both gives the result in boolean or tinyint(1). If the condition becomes true, then the result will be 1 otherwise 0.
The problem is that a != b is NULL when either a or b is NULL.
<=>
is the NULL-safe equals operator. To get a NULL-safe not equal to you can simply invert the result:
SELECT *
FROM my_table
WHERE NOT column_a <=> column_b
Without using the null safe operator you would have to do this:
SELECT *
FROM my_table
WHERE column_a != column_b
OR (column_a IS NULL AND column_b IS NOT NULL)
OR (column_b IS NULL AND column_a IS NOT NULL)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With