SQL Inner Join On Null Values

Question

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   Anyone have a work around that doesn t entail modifying the data

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Inner Join On Null Values

Examples related to sql

Examples related to sql-server

Examples related to join

Examples related to null