Mariadb query variable with null value returns nothing

Question

I'm trying to select some rows when a specific column are equal to my variable. Some of these rows have that column set as null.

For this, i made a query using a variable to get that match

SELECT

MT.Column1, 
MT.Column2, 
MT.SpecificColumn

FROM

MyTable MT

WHERE

MT.SpecificColumn = :Variable

So, it happens that when this variable receives NULL, the query doesn't return the rows that have the SpecificColumn value NULL.

The only way i got the rows was using this condition:

WHERE

MT.SpecificColumn IS NULL

but that case doesn't fit to me, because i need it to be dynamic.

In some cases it will be null, and that is expected. What i need is to get the rows with that null column when i try to match the variable set as null with the column. — Gabriel Marques
– Gabriel Marques, Commented Sep 5, 2019 at 16:18
Check the null safe operator: mariadb.com/kb/en/library/null-safe-equal — forpas
– forpas, Commented Sep 5, 2019 at 16:19
How badly does it harm query execution if you COALESCE both sides of the WHERE predicate? — tbrookside
– tbrookside, Commented Sep 5, 2019 at 16:32

Georg Richter · Accepted Answer · 2019-09-08 10:13:11Z

1

From Codd's rules

Systematic treatment of null values:

Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

So you cannot compare missing data, but you can check if data is missing.

If you want to check NULL without IS NULL, you need to transform it, e.g.

SELECT fields FROM MyTABLE WHERE IFNULL(MT.SpecificColumn, "NULL VALUE")=@variable

If you set @variable="NULL VALUE" it will find the NULL values. If it's effective or not is of course another question.

answered Sep 8, 2019 at 10:13

Georg Richter

7,6442 gold badges19 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mariadb query variable with null value returns nothing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related