SELECT from database when i don´t know position of null values

Good afternoon. I need your help to make correctly query into database.

I have following table in DB with adresses, eg.

id	street	house_nuber	city	district
1	First street	1225	City One	NULL
2	NULL	25	Small city	NULL
3	Second street	51	Roswell	District nine
4	Third street	15963	Last city	Another district

In the table above you can see i have adresses. I also have random NULL values in rows. My table has about 50k records for now.

PHP code fill variables with following values

$street = 'First street';
$houseNum = 1125;
$city = 'City One';
$district = NULL;

I need to return ID from database of matching adress. If i know, where NULL value is, query is easy.

SELECT id FROM adress WHERE street=$street AND house_number=$houseNum AND city=$city AND district IS NULL

(normaly i use PDO, this is only for ilustration)

And now my question. How to make query, If i can´t predict, where NULL value is? I tryed to make query with empty strings, but for database is not same the NULL and empty and i always had false return.

Thak you and have a nice day.

Answer

To match exactly rows in your table, you can use NULLIF() to convert your PHP empty strings to SQL NULL values and NULL-safe operator to compare NULL to them without NULL result.

Ex. :

$sql = <<<SQL
    SELECT id 
    FROM adress 
    WHERE TRUE 
        AND street <=> NULLIF({$pdo->quote($street)}, '')
        AND house_number <=> NULLIF({$pdo->quote($houseNum)}, '')
        AND city <=> NULLIF({$pdo->quote($city)}, '')
        AND district <=> NULLIF({$pdo->quote($city)}, '')
    SQL;