SQL Inner Join On Null Values

I have a Join

SELECT * FROM Y
INNER JOIN X ON ISNULL(X.QID, 0) = ISNULL(y.QID, 0) 

Isnull in a Join like this makes it slow. It’s like having a conditional Join.
Is there any work around to something like this?
I have a lot of records where QID is Null

  • Convert String to date in select statement
  • Restore same database multiple times
  • Query age of a current value in table with historical records
  • Determine Active Node in SQL Failover Cluster
  • Controlling file extension when inserting data into SQL Filestream column?
  • I can't add Microsoft.SqlServer.Management.Common to my ASP.NET MVC Application
  • Anyone have a work around that doesn’t entail modifying the data

  • SQL Server 2008 update table with values from another table
  • How to transform a row to a column and add it to the result set
  • Joining two SQL queries with different variations of data
  • Having Trouble Converting SQL to LINQ when using a join on a select statement
  • Include records that has a null value on INNER JOIN SQL Server query over a tree table
  • Insert a new line based on the existing table data (SQL Server 2008 R2)
  • 7 Solutions collect form web for “SQL Inner Join On Null Values”

    You have two options

    INNER JOIN x ON x.qid = y.qid OR (x.qid IS NULL AND y.qid IS NULL)

    or easier

    INNER JOIN x ON x.qid IS NOT DISTINCT FROM y.qid

    Are you committed to using the Inner join syntax?

    If not you could use this alternative syntax:

    SELECT * 
    FROM Y,X
    WHERE (X.QID=Y.QID) or (X.QUID is null and Y.QUID is null)
    

    This article has a good discussion on this issue. You can use

    SELECT * 
    FROM Y
    INNER JOIN X ON EXISTS(SELECT X.QID 
                           INTERSECT 
                           SELECT y.QID);
    

    I’m pretty sure that the join doesn’t even do what you want. If there are 100 records in table a with a null qid and 100 records in table b with a null qid, then the join as written should make a cross join and give 10,000 results for those records. If you look at the following code and run the examples, I think that the last one is probably more the result set you intended:

    create table #test1 (id int identity, qid int)
    create table #test2 (id int identity, qid int)
    
    Insert #test1 (qid)
    select null
    union all
    select null
    union all
    select 1
    union all
    select 2
    union all
    select null
    
    Insert #test2 (qid)
    select null
    union all
    select null
    union all
    select 1
    union all
    select 3
    union all
    select null
    
    
    select * from #test2 t2
    join #test1 t1 on t2.qid = t1.qid
    
    select * from #test2 t2
    join #test1 t1 on isnull(t2.qid, 0) = isnull(t1.qid, 0)
    
    
    select * from #test2 t2
    join #test1 t1 on 
     t1.qid = t2.qid OR ( t1.qid IS NULL AND t2.qid IS NULL )
    
    
    select t2.id, t2.qid, t1.id, t1.qid from #test2 t2
    join #test1 t1 on t2.qid = t1.qid
    union all
    select null, null,id, qid from #test1 where qid is null
    union all
    select id, qid, null, null from #test2  where qid is null
    

    If you want null values to be included from Y.QID then Fastest way is

    SELECT * FROM Y
    LEFT JOIN X ON y.QID = X.QID

    Note: this solution is applicable only if you need null values from Left table i.e. Y (in above case).

    Otherwise

    INNER JOIN x ON x.qid IS NOT DISTINCT FROM y.qid

    is right way to do

    Basically you want to join two tables together where their QID columns are both not null, correct? However, you aren’t enforcing any other conditions, such as that the two QID values (which seems strange to me, but ok). Something as simple as the following (tested in MySQL) seems to do what you want:

    SELECT * FROM `Y` INNER JOIN `X` ON (`Y`.`QID` IS NOT NULL AND `X`.`QID` IS NOT NULL);
    

    This gives you every non-null row in Y joined to every non-null row in X.

    Update: Rico says he also wants the rows with NULL values, why not just:

    SELECT * FROM `Y` INNER JOIN `X`;
    

    You could also use the coalesce function. I tested this in PostgreSQL, but it should also work for MySQL or MS SQL server.

    INNER JOIN x ON coalesce(x.qid, -1) = coalesce(y.qid, -1)
    

    This will replace NULL with -1 before evaluating it. Hence there must be no -1 in qid.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.