Not matched rows in B join with some row in A

Question

Example data:

Table1     Table2
x | y      x | y  
-----      -----
1 | A      1 | A
1 | B      1 | D
1 | C      2 | M
1 | null   2 | N
2 | M      2 | M
2 | N      1 | A

I want to count number of x's in Table2 and join it with Table1. But in Table2 I can have y's which doesn't match to y's in Table1. In this case I want to join them with row with null in y column in Table1. In an example Table2.1-D should be joined with Tabl1.1-null

For given example I expect the result:

x | y    | count
-----------------     
1 | A    | 2
1 | B    | 0
1 | C    | 0
1 | null | 1 (because D doesn't match to anything else in Table1)
2 | M    | 2
2 | N    | 1

Possible duplicate of [Difference between left join and right join in SQL Server](https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server) — S3S, Dec 06 '17 at 17:47
@massther . . . This ranks as one of the more interesting questions that I've seen in a while. — Gordon Linoff, Dec 06 '17 at 17:49
haha Thank you :) Unfortunately, this is not a matter of use left or right join. — massther, Dec 06 '17 at 18:08

Gordon Linoff · Answer 1 · 2017-12-07T01:22:08.610

2

I can't think of a really elegant way to accomplish this. The following is somewhat brute force, but it gets the job done:

select t1.x, t1.y,
       (case when t1.y is null then unmatched.cnt else matched.cnt end) as cnt
from table1 t1 outer apply
     (select count(*) as cnt
      from table2 t2
      where t2.y = t1.y
     ) matched cross join
     (select count(*) as cnt
      from table2 t2
      where not exists (select 1 from table1 t1 where t1.y = t2.y)
    ) unmatched;

edited Dec 07 '17 at 01:22

answered Dec 06 '17 at 17:48

Gordon Linoff

1,242,037
58
646
786

It looks like your solution is quite good. The execution plan looks better than for other solutions. Thank you :) – massther Dec 06 '17 at 18:09
You have a table alias typo in the first subquery after the `outer apply`. `t.y` should be `t2.y`. – digital.aaron Dec 06 '17 at 19:06
If in Table 2 are rows which should be match to row Table1.y == null for more that one x, the query counts it incorrectly. – massther Dec 06 '17 at 19:10
1

@digital.aaron . . . Actually, it should be `t1.y`. – Gordon Linoff Dec 07 '17 at 01:22

score 0 · Answer 2 · edited Dec 06 '17 at 17:57

There are multiple ways to solve this problem and the best one for you really depends on the data, the number of rows and resulting performance.

One of the ways would be doing the operation for everything but null from the Table1 and then UNION-ing with the null row.

For your given example I assume that the combination of x and y values in both Table1 and Table2 is always unique. You'll need additional grouping if it's not.

SELECT
    t1.x,
    t1.y,
    COUNT(*) as [count]
FROM
    Table1 t1
    INNER JOIN Table2 t2 ON t1.y = t2.y
WHERE
    t1.y IS NOT NULL
GROUP BY
    t1.x, t1.y
UNION ALL
SELECT
    t1.x,
    NULL as y,
    t2missingFromt1.[count]
FROM
    Table1 t1
    CROSS APPLY (
        SELECT
             COUNT(x) as [count]
        FROM
             Table2 t2
        WHERE NOT EXISTS (SELECT 1 FROM Table1 tbl1 WHERE tbl1.y = t2.y)
    ) t2missingFromt1
WHERE
    t1.y IS NULL

If you look at the sample data, you can see x and y combinations are not unique in Table2. — digital.aaron, Dec 06 '17 at 18:05

score 0 · Answer 3 · answered Dec 06 '17 at 18:04

This is the best I came up with:

SELECT t1.x
, t1.y 
, [count] = ISNULL(t2.cnt,0)
FROM #Table1 t1
LEFT JOIN (SELECT x
            , y
            , cnt = COUNT(*) 
            FROM #Table2
            GROUP BY x, y
        ) t2 
ON (t1.x = t2.x 
        AND t1.y = t2.y 
        AND t1.y IS NOT NULL
    ) 
    OR (t1.x = t2.x 
        AND ISNULL(t1.y,t2.y) = t2.y 
        AND NOT EXISTS (SELECT 1 FROM #Table1 WHERE y = t2.y)
    )

score 0 · Answer 4 · answered Dec 06 '17 at 18:21

Here is one solution. See if this helps!

WITH v_t2 AS
(SELECT y, COUNT(*) AS cnt
   FROM table2 t2 
  GROUP BY t2.y
),
v_t2_null AS
(SELECT COUNT(*) AS cnt
   FROM table2 t2
  WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.y= t2.y)
)
SELECT t1.x. t1.y, COALESCE(t2.cnt, v_t2_null.cnt)
  FROM table1 t1 LEFT JOIN v_t2 t2
                   ON (t1.y = t2.y)
                 JOIN v_t2_null;

Output

    x   y   cnt
    1   A   2
    1   B   0
    1   V   0
    1   NULL    1
    2   M   2
    2   N   1

score 0 · Answer 5 · answered Dec 06 '17 at 18:25

If my understanding is right, it is not just a left join question, it is something like this:

-- Creating example
CREATE TABLE #T1 (x int, y char(1) null)
CREATE TABLE #T2 (x int, y char(1) null)

-- Loading tables
INSERT INTO #T1
SELECT 1,'A'  
UNION ALL SELECT 1,'B'  
UNION ALL SELECT 1,'C'  
UNION ALL SELECT 1,null 
UNION ALL SELECT 2,'M'  
UNION ALL SELECT 2,'N'  

INSERT INTO #T2
SELECT 1 ,'A'
UNION ALL SELECT 1 ,'D'
UNION ALL SELECT 2 ,'M'
UNION ALL SELECT 2 ,'N'
UNION ALL SELECT 2 ,'M'
UNION ALL SELECT 1 ,'A'


SELECT t1.x, t1.y, count(t2.y) FROM #T1 t1
LEFT JOIN #T2 t2 on t1.y = t2.y 
LEFT JOIN #T2 t22 on t1.Y IS NULL AND  t22.y IS NULL
WHERE t1.y is not null
GROUP BY t1.x, t1.y
UNION ALL
SELECT t.x, t.y, (SELECT count(1) 'count' FROM #T2 t2 
LEFT JOIN #T1 t1 on t1.y = t2.y  
WHERE t1.y IS NULL
) FROM #T1 t WHERE t.y IS NULL

I had to separate the problem in two.

score 0 · Answer 6 · answered Dec 06 '17 at 18:57

Another way we can do this via a self join and group by on join of two tables like below

select t1.*,t2.c from Table1 t1 join
(
    select 
        a.y,
        sum(case when b.y is null then 0 else 1 end)  as c
    from Table1 a 
        full outer join 
        Table2 b 
    on 
        a.y=b.y
    group by a.y
)t2
on ISNULL(t1.y,-1)=ISNULL(t2.y,-1)

Working demo

score 0 · Answer 7 · answered Dec 06 '17 at 19:06

Thank you for all answers. They were very helpful. Here is my solution, with counts in proper way all rows from Table2 and match it by x to row in table1 with y == null. I know that I can wright better subquery in left join.

Of course the real problem is much complicated and the question is simplified version of it.

I you find how I can improve the query then please, let me know :)

;with T2Count as
(
    select x, y, count(*) cnt from @t2 group by x, y
)
/*
select t1.x, t1.y, isnull(t2.cnt, 0)
from @T1 t1
left join T2Count t2 on t1.x = t2.x and t1.y = t2.y
where t1.y is not null
*/
select t1.x, t1.y, isnull(AllCounts.cnt, 0) cnt from @t1 t1
left join (
    select * from T2Count -- I know that here I'will take too much data 1-D, 2-Q, but those data will not natch in last join (AllCounts...)
union all
    select t2.x, null, sum(cnt) cnt
    from T2Count t2
    left join @t1 t1 on t1.x = t2.x and t1.y = t2.y
    where t1.x is null
    group by t2.x
) as AllCounts on t1.x = AllCounts.x and (t1.y = AllCounts.y or (t1.y is null and AllCounts.y is null))

Not matched rows in B join with some row in A

7 Answers7