Delphi null parameter in where

Question

I have two ZQuery's - master and details, linked via detail.DataSource and sql parameters.

Master sql:

select key1, key2 from list

Details sql:

select * from list where key1=:key1 and key2=:key2

The problem is that when key2 is null the details is empty!

How do I get the parameters here work with null link fields?

Answer 1

SQL uses 3-valued logic. All your boolean values can have 3 states: true, false and null (undefined). The null is a false value is a where clause. You can look up for the thruth tables for this logic here.

In the expression key1=:key1 and key2=:key2, if any of the columns have null value, the whole expression evaluates to null.

I advise you to use the IS DISTINCT FROM or IS NOT DISTINCT FROM operators if your DBMS supports them. They can return only true or false, even if one of their argument is null.

Basically IS NOT DISTINCT FROM is the more readable and standard way of writing x = y OR (x IS NULL AND y IS NULL).

Answer 2

Then try to add into your WHERE clause a condition that allows you to fetch also tuples that are at NULL state in their field:

SELECT
   *
FROM
   LIST
WHERE
   KEY1 = :KEY1 AND
   (KEY2 IS NULL OR KEY2 = :KEY2)