Compare nulll value with non null value SQL-CodePudding

I have two columns as below:

When i query these columns as below :

SELECT Column A, Column B from table where Column A != Column B

i am expecting the following result:

Column A	Column B
A1	NULL
B1.	C1

But my query is only giving me the second line as result.

CodePudding user response：

Null values do not participate in equality operations as there is no value to compare with.

You will need to include a check for null into your comparisoon statement.

SELECT Column A, Column B from table where  ISNULL(Column A,0) <> ISNULL(Column B,0)

CodePudding user response：

null in SQL equals unknown

It could also have A1 as value, therefore it will not show up if you check if A1 is not null

CodePudding user response：

SELECT * from Table where IFNULL(`Column A`, 'NULL') != IFNULL(`Column B`, 'NULL')

CodePudding user response：

You can use below workaround

select * from your_table
except distinct
select * from your_table
where columnA = columnB

with output