SQL Inner Join on Null Values

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

Inner JOIN with null ids

Here is what i came up with.

I used the sailorsenshi table as kind of a base table, then changed out your inner joins to left joins, then made sure the sailorsenshi was on the left side of the join clause. Also, i noticed you didn't alias the cat column, and the school column wasn't present so i added them in there.

select 
    senshi_name sailor_senshi
    ,real_name_jpn real_name
    ,cats.name cat
    ,schools.school school
from sailorsenshi 
LEFT JOIN 
  cats ON (sailorsenshi.cat_id = cats.id)
LEFT JOIN schools ON (sailorsenshi.school_id = schools.id);

This should return your expected results.

SQL Inner join including NULL values

You can use TOP 1 WITH TIES on a ROW_NUMBER sort to get the records with the latest dates per customer only.

select 
  c.customerid,
  c.name,
  a.countrycode,
  case when c.actorid in 
    (select * from agreement where activeagreement = 'Y' and product in ('6774', '6775'))
   then 1 else 0 end as hasaccount,
  i.controldate,
  i.controlby
from customer c
left join agreement a on a.actorid = c.actorid
left join 
(
  select top 1 with ties *
  from identification
  order by row_number() over (partition by actorid order by controldate desc)
) i on i.actorid = c.actorid
where c.customerstatus = 'Active';

UPDATE: Above answer didn't work for the OP, so I offered the following two alternatives that did work:

left join
(
  select
    actorid, controlby, controldate,
    max(controlby) over (partition by actorid) as max_controldate
  from identification
) i on i.actorid = c.actorid and i.controldate = i.max_controldate. 

and

left join
(
  select *
  from identification
  qualify row_number() over (partition by actorid order by controldate desc) = 1)
) i on i.actorid = c.actorid. – Thorsten

The last option with QUALIFY is the teradata way to do this. QUALIFY is a teradata extension to the SQL standard. The other two approaches are standard SQL.

inner join two tables having null values on the foreign keys of each other

You should using LEFT OUTER JOIN to get the specified result.

Include records that has a null value on INNER JOIN SQL Server query over a tree table

Change the INNER JOIN to a LEFT JOIN:

SELECT DISTINCT <valuesThatINeed>
, CASE 
     WHEN barN.id IS NULL 
         THEN 'Your previous level with BarID = ' 
               + CAST(barN-1.id AS NVARCHAR(MAX)) 
               + ' is the deepest level'
      ELSE ''
  END
FROM atable foo
   LEFT JOIN treetable bar ON foo.id_bar = bar.id 
   LEFT JOIN treetable bar2 ON bar.id_bar = bar2.id OR (bar.id_bar IS NULL) 
   LEFT JOIN treetable bar3 ON bar2.id_bar = bar3.id
   ...
   LEFT JOIN treetable barN ON barN-1.id_bar = barN.id
WHERE
   <constraints>

You can continue making LEFT joins until you reach a depth where your barN.id IS NULL, which will mean that you have reached the deepest level at N-1.

But if you're trying to make a hierarchy, then this is not the scalable way to go. Do a web-search for recursive CTEs (here is a possible hint which you can try to adapt to your situation).

---

.

SQL INNER JOIN WHEN NULL VALUES

select * from notification n
    inner join user u 
    on (n.idUserNotifier=u.idUser)
    inner join advice a on case WHEN n.idAdviceOwner IS NOT NULL  
                           THEN n.idAdviceOwner else n.idAdviceNotifier END = a.idAdvice
    where n.idUser=8;

Your idea was right but CASE-WHEN-ELSE-END returns an expression, not a condition. That's why you received the error.

INNER JOIN clause ignoring NULL values

You are actually looking for LEFT JOIN:

SELECT
    pd.fname,
    pd.lname,
    pp.drug_name,
    pp.drug_strength
FROM
    patient_data pd 
     FULL OUTER JOIN patient_prescr pp on pp.pid = pd.pid 
     FULL OUTER JOIN formulary f on pp.med_id = f.id 
     LEFT JOIN formulary_categories fc on f.category = fc.id 
         AND fc.id in (34,36,37,38,5)
WHERE
    pd.lname = 'Test'

A LEFT JOIN will not filter data if a correlation is not found between the values in the two tables (or result sets) and will display a NULL value for the columns which displays data from the table where a correlation was not found (just like in your expected output sample).

You can also take a look at the best article (in my opinion) for understanding all types of JOINs, here.

MySQL Inner Join with null columns

Yes like this

select a.brand, b.boxId 
from a prod
inner join b box on a.fruit = b.fruit
where ( a.edible = b.edible OR a.edible IS NULL OR b.edible IS NULL ) and 
      ( a.color = b.color OR a.color IS NULL OR b.color IS NULL ) and 
      ( a.size = b.size OR a.size IS NULL OR b.size IS NULL)

SQL join by non null values

You may add conditions to the ON clause of the join which match on either a value or match or a NULL:

SELECT fi.*
FROM Filters fi
INNER JOIN Forms fo
    ON fi.filter_1 = fo.id AND
       (fo.name = fi.filter_2 OR fi.filter_2 IS NULL) AND
       (fo.title = fi.filter_3 OR fi.filter_3 IS NULL);

Demo

---

Related Topics