How to bypass NULL in a Lookup Table using JOIN?

Question

In SQL (SSMS), I am trying to do a lookup using a ranging table (similar to Point_Lookup below) and what I need is to bypass the NULL scenario.

Firstly, please use below SQL codes to replicate the scenario in question:

-- Code for People Data --
Create table #People ([Name] varchar(50) null,Age int null)

Insert into #People VALUES
('George' , 30),
('Clooney' , 18),
('Sandra' , 44),
('Bullock' , 15),
('Adam' , 100)

-- Code for Point_Lookup Data--
Create table #Point_Lookup ([Lower_Limit] int null, [Upper_Limit] int null, [Point] int null)

Insert into #Point_Lookup VALUES
(0, 10, 1),
(10, 20, 2),
(20, 30, 3),
(30, 40, 4),
(40, 50, 5),
(50, NULL, 6)

I have tried below code to successfully join both tables and get the desired points EXCEPT when [Age] >= 50 (Since the Upper_Limit is showing NULL, the point from the lookup table is also showing NULL - desired result should be 6).

Select ppl.*, point.[Point]
from #People as ppl
left join #Point_Lookup as point 
on ppl.[Age] >= point.[Lower_Limit] and 
ppl.[Age] < point.[Upper_Limit]

I have also tried replacing the NULL using ISNULL() but I realized this still does not JOIN both tables when [Age] >= 50 (not quite sure why).

Select ppl.*, point.[Point]
from #People as ppl
left join #Point_Lookup as point 
on ppl.[Age] >= point.[Lower_Limit] 
and ppl.[Age] < isnull(point.[Upper_Limit], point.[Upper_Limit] + 1 + ppl. 
[Age])

Is there a way to somehow only consider one condition --> (ppl.[Age] >= point.[Lower_Limit]) when [Age] >= 50 (without going into the NULL in Upper_Limit)? Maybe somehow using CASE?

The expected result should show 6 Point when [Age] >= 50. Please help.

Why don't you set a high enough upper limit on the row with 50? — EzLo, Feb 13 '19 at 08:07
In the real table, I'm dealing with prices where the connector is Product Department. Prices are unpredictable and maximum price cannot be fixed :( I tried using +1 from [Age] (or [Price] in the real table) in essence giving something that's always bigger than [Age].. — mlinardy, Feb 13 '19 at 08:11
I had a similar scenario for price brackets (the unit price depends of ranges of units bought) added to the logic to bypass the null in the limits, you will need to add a logic to avoid lacks or overlaps in your ranges, check my solution.... — Daniel Brughera, Feb 13 '19 at 08:52

score 3 · Accepted Answer · edited Feb 13 '19 at 08:16

3

You can try using coalesce() function which will work like case when, so if point.[Upper_Limit] is null then it will consider later one

    Select ppl.*, point.[Point]
    from #People as ppl
    left join #Point_Lookup as point 
    on ppl.[Age] >= point.[Lower_Limit] 
    and ppl.[Age] < coalesce(point.[Upper_Limit], point.[Lower_Limit] + 1 + ppl. 
    [Age])

edited Feb 13 '19 at 08:16

mlinardy

37
4

answered Feb 13 '19 at 08:07

Fahmi

37,315
5
22
31

2

Thanks @fa06 .. I'm assuming your second point.[Upper_Limit] should be point.[Lower_Limit] (in coalesce)? Then it's working. Thanks for the suggestion. – mlinardy Feb 13 '19 at 08:15
3

Better than `COALESCE` will be an explicit `(blah IS NULL OR SomeOtherCondition)`. Read about [SARGability](https://stackoverflow.com/q/799584/5089204). EzLo's answer points to this fact with *better for index usage*... – Shnugo Feb 13 '19 at 08:23

EzLo · Answer 2 · 2019-02-13T08:18:03.930

If NULL means that the condition should be avoided then you can use an OR to write exactly this:

Select 
    ppl.*, 
    point.[Point]
from 
    #People as ppl
    left join #Point_Lookup as point on 
        ppl.[Age] >= point.[Lower_Limit] and 
        (point.[Upper_Limit] IS NULL OR ppl.[Age] < point.[Upper_Limit])

In you attempt:

isnull(point.[Upper_Limit], point.[Upper_Limit] + 1 + ppl.[Age])

If point.[Upper_Limit] is NULL then any addition will also be NULL, that's why it doesn't join correctly. You should remove the point.[Upper_Limit] and just leave 1 + ppl.[Age] and it will work, but using the OR will be better for index usage (if any).

Thank you! If I could mark this as a great answer too, I would! :( You've also given the explanation why it didn't work. You're a superstar! — mlinardy, Feb 13 '19 at 08:25

score 0 · Answer 3 · answered Feb 13 '19 at 08:29

0

try this,

Select ppl.*,
(select top 1 point from #Point_Lookup where ppl.Age>=Lower_Limit and ppl.Age<=(case when Upper_Limit is null then ppl.Age else Upper_Limit end) ) from #People as ppl

answered Feb 13 '19 at 08:29

Anoos

186
7

score 0 · Answer 4 · answered Feb 13 '19 at 08:43

The problem with this structure is that you can have lacks or overlaps in the ranges, also, in this case can be confusing which should be the correct point for a value equal to one of the limits...

I use to do it this way, with one single limit, the boundaries are defined by the previous and next records, and there is no way to have a lack of values out of the range

Create table #People ([Name] varchar(50) null,Age int null)

Insert into #People VALUES
('George' , 30),
('Clooney' , 18),
('Sandra' , 44),
('Bullock' , 15),
('Adam' , 100),
('Lio' , 4)
-- Code for Point_Lookup Data--
Create table #Point_Lookup ([Limit] int not null,[Point] int null)

Insert into #Point_Lookup VALUES
(0, 1),
(10, 2),
(20, 3),
(30, 4),
(40, 5),
(50, 6)

SELECT *
FROM
#People P
CROSS APPLY (
    SELECT TOP 1 Point 
    FROM #Point_Lookup L 
    WHERE P.Age >= L.Limit
    ORDER BY Limit DESC
) L

drop table #people
drop table #Point_Lookup

How to bypass NULL in a Lookup Table using JOIN?

4 Answers4