How to write condition "x = y" such that it is true when both x and y are NULL?

I am using the following query:

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type
FROM tblAccounts
    INNER JOIN tblTxn ON (tblAccounts.Currency = tblTxn.Currency)
WHERE
    tblTxn.Type=tblAccounts.Type OR 
    (tblAccounts.Type Is Null And tblAccounts.Type Is Null);

Here tblTxn.Type and tblAccounts.Type can have values or null values.

I need both to match. Null values are showing expected result but values aren't.

How to fix this?

I was using the query in MS Access 2007. From the below solutions I adjusted the query like:

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type FROM tblAccounts INNER JOIN tblTxn ON tbl开发者_JAVA百科Accounts.Currency = tblTxn.Currency WHERE Nz(tblTxn.Type,0) = Nz(tblAccounts.Type,0)

It is showing the expected result now. Thanks for excellent support. NB.(Function Nz(Value, [ValueIfNull]) is COALESCE alternative in Access.)

One thing I would like to know, if I use WHERE tblTxn.Type=tblAccounts.Type OR Nz(tblTxn.Type,0) = Nz(tblAccounts.Type,0), it shows the same result. How did your query worked with non-null values while you were matching null values only?

Your query checks tblAccounts.Type Is Null twice. The query would work if you replace one tblAccounts.Type Is Null condition with tblTxn.Type Is Null.

Also you could do:

DECLARE @FakeString VARCHAR(10)
SET @FakeString = '!@#$%^&'

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type
FROM tblAccounts 
    INNER JOIN tblTxn ON tblAccounts.Currency = tblTxn.Currency
WHERE 
    ISNULL(tblTxn.Type, @FakeString) = ISNULL(tblAccounts.Type, @FakeString)

If value is integer, you could do something like this

DECLARE @FakeType INT
SET @FakeType = -9999

You'll need to use IsNull to check for null values.

See MSDN: ISNULL (Transact-SQL) (assuming SQL Server)

Not sure if this will exactly meet your requirement - but should point you to right direction.

SELECT  tblTxn.Currency, 
    tblTxn.Amount, 
    tblTxn.Desc, 
    tblAccounts.Account, 
    tblTxn.Type 
FROM    tblAccounts 
        INNER JOIN tblTxn ON (tblAccounts.Currency = tblTxn.Currency) 
WHERE   IsNull(tblTxn.Type, '') =IsNull(tblAccounts.Type,'') 
    OR COALESCE(tblAccounts.Type, tblAccounts.Type) Is Null;

SELECT tblTxn.Currency
, tblTxn.Amount
, tblTxn.Desc
, tblAccounts.Account
, tblTxn.Type
FROM tblAccounts
INNER JOIN tblTxn ON tblAccounts.Currency = tblTxn.Currency
WHERE ISNULL(tblTxn.Type,'') = ISNULL(tblAccounts.Type,'')
--  This works as well
--  WHERE COALESCE(tblTxn.Type,'') = COALESCE(tblAccounts.Type,'')

Using the ISNULL and returning the empty string will let your keys that are NULL be matched. The danger there is that you will likely have more matches than are really relevant. But, the query provided should suit you either way.