SQL non-duplicate join to table which uses nulls as wildcards

Question

I'm querying tables which use a null value as wildcard, this can be overridden if another row contains a non-wildcard value. E.g.:

Table 1: Customers - containing customers and associated products:

create table #cust (
    id int not null,
    product varchar(3) not null
)

insert into #cust
values (1,'ABC'),(1,'DEF'),(1,'GHI')

============
id | product
------------
1  | ABC
1  | DEF
1  | GHI
============

Table 2: Rebates - contains the rebate customers receive for each product. A null product field specifies a default rebate, to be applied to all products except any which are overtly specified:

create table #rebate (
    cust_id int not null,
    product varchar(3) NULL,
    rebate numeric(5,2) not null
)

insert into #rebate
values (1,null,0.25),(1,'ABC',0.05)

==========================
cust_id | product | rebate
--------------------------
1       | null    | 0.25
1       | ABC     | 0.05
==========================

So this customer a receives a 25% rebate for all products except 'ABC' for which they receive 5%.

I was trying to code it in a simple way like this:

select * 
from #cust c
left join #rebate r
    on c.id = r.cust_id
        and c.product = isnull(r.product, c.product)

However, the result of this is duplication on the ABC product (matching the r.product is null, and the r.product = c.product parts of the join):

======================================
id  product cust_id  product    rebate
--------------------------------------
1   ABC     1        NULL       0.25    -- duplication
1   DEF     1        NULL       0.25
1   GHI     1        NULL       0.25
1   ABC     1        ABC        0.05    -- duplication. This is the row needed
=======================================

Any suggestions?

So what is the logic you want here? The 2 rows aren't duplicates, as they are distinctly different; the values of `product` and `rebate` differ. The `JOIN` is also working as expected, as if a `JOIN` has 2 matching rows in another table, you get 2 rows, not 1. — Thom A, Jul 29 '21 at 09:12
What do you want to achieve? A price list for a specific customer? — Peter Koltai, Jul 29 '21 at 09:15
Thanks guys I agree with the above - the SQL is working as per the logic, but the output required is a single rebate per customer, per product - a *specified* product rebate should override a default rebate. The suggestions below from Marc Guillot and Thorsten Kettner are both along the right lines. Am testing them both now. Thanks again! — Peter Johnson, Jul 29 '21 at 09:52
Side notes: In the real world, you shouldn't call the table #cust of course, as this is not the customer table (with one row per customer). You may call that `customer_product` for instance. And the column `id` should not be called `id`, because if it were the table's ID there would be only one row per ID in the table. You should call it `cust_id` or the like. — Thorsten Kettner, Jul 29 '21 at 09:52
Yeah Thorsten I do have a single customer table and a single prod table (and about 300 others in the database!). I only formatted it like that to make it easy to understand. In the real application, there is a customer table, a product table and a transaction table. Each customer can make a transaction against any product and the rebate table specifies the rebate a specific customer will receive - in general and sometimes product specific. But I like your pedantic nature - the sign of a proper guru! — Peter Johnson, Jul 29 '21 at 10:16
If the number of product specific rebates tends to be significantly lower than the number of products per customer, I would use the double-left-join approach but keep the rebates in two tables (customer level rebate, and customer-product level rebate) rather than attempt to collapse them in to one. I'd expect it to be easier to understand, easier to maintain, easier to adapt, etc, etc. — MatBailie, Jul 29 '21 at 11:18

Marc Guillot · Answer 1 · 2021-07-29T09:45:26.350

1

You can join join the rebates conditionally. You first join the specific rebates, and then join the default rebates for the products where no specific rebate has been found.

select c.*, coalesce(specific_r.rebate, default_r.rebate) as rebate
from #cust c
     left join #rebate specific_r on 
               c.id = specific_r.cust_id and c.product = specific_r.product
     left join #rebate default_r on 
               c.id = default_r.cust_id and default_r.product is null and 
               specific_r.cust_id is null  -- No specific rebate found

This gives you the desired result :

==================
id  product rebate
------------------
1   ABC     0.05
1   DEF     0.25
1   GHI     0.25
==================

edited Jul 29 '21 at 09:45

answered Jul 29 '21 at 09:22

Marc Guillot

6,090
1
15
42

Thanks Marc. I like this a lot, but Thorsten's solution below is just a wee bit more elegant in my view. What do you think? – Peter Johnson Jul 29 '21 at 10:03
1

@PeterJohnson Try both and test for performance, they are both common solutions to this type of problem – Charlieface Jul 29 '21 at 12:56
@PeterJohnson Thorsten solution is a correlated subquery, so my proposal should give you better performance. His solution is very elegant, but I would use it as a ranked CTE, like proposed here : https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – Marc Guillot Jul 29 '21 at 13:06

score 0 · Accepted Answer · edited Jul 29 '21 at 10:34

You want ranking. There are general rebates and particular rebates, and you prefer the particular over the general one, if it exists. There are several ways to approach this and they mostly boil down to selecting the candidates (which is one or two rows per customer) and then pick the better one. Here is a solution with a lateral join (called OUTER APPLY in SQL Server):

select * 
from #cust c
outer apply
(
  select top(1) * -- TOP(1) picks the row brought up first by ORDER BY
  from #rebate r
  where r.cust_id = c.id
  and (r.product = c.product or r.product is null)
  order by r.product desc -- DESC orders NULLs last in SQL Server
) rr;

SQL Server lacks a NULLS LAST clause for ORDER BY, but you get nulls last by sorting decending.

Wunderbar Thorsten. Very many thanks for this nice solution – Peter Johnson Jul 29 '21 at 10:02 — Peter Johnson, Jul 29 '21 at 10:02

SQL non-duplicate join to table which uses nulls as wildcards

2 Answers2