SQL Server query with intersect except or union relational Algebra

Question

I am trying to solve a problem. It seems that of a brain teaser if you ask me.

Given two tables, return only values from the first table when there is a match for EVERY record in a second table. So a record in table 1 must have a match to every record in table 2. If table 2 has fewer than every row I want to exclude it from the final result.

This must be done without using count, having, group by. I must solve it with union, intersect, except, exists.

I am using SQL Server BTW.

CREATE TABLE table1 (id int, lid int)
INSERT INTO table1  VALUES (1, 1),(1, 2),(1,3),(1,4),(2,1),(3,3),(4,4)

CREATE TABLE table2 (lid int)
INSERT INTO table2 VALUES (1),(2),(3),(4)

Table 1:

id  lid
--------
1   1
1   2
1   3
1   4
2   1
3   3
4   4

Table2:

lid
-----
1
2
3
4

This method here is "not the way I am supposed to solve it". Frustrating because this solution is so simple and does exactly what it should do. I can't use count, group by, and having.

SELECT id 
FROM dbo.table1, dbo.table2
WHERE table1.lid = table2.lid
GROUP BY id
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.table2)

So basically I need to find a way to exclude the results from the first table when there is not a full set of matches in table 2. In this example the only value in table 1 with a match to every record in table 2 is 1. 2,3,4 would need to be excluded.

The name for what you want to do is relational division. If you browse that tag you will certainly find solutions meeting your criteria https://stackoverflow.com/questions/tagged/relational-division — Martin Smith, Sep 20 '17 at 11:55

score 3 · Accepted Answer · answered Sep 20 '17 at 12:23

What you're looking for has a name. It's called relational division. It has no equivalent in SQL, although it can be emulated in a variety of ways. Joe Celko has written one of the most complete blog posts about the topic.

Since you must use some of the more basic relational operators in SQL, this could be one solution for you:

SELECT DISTINCT id
FROM table1 t1a
WHERE NOT EXISTS (
  SELECT *
  FROM table2
  WHERE NOT EXISTS (
    SELECT *
    FROM table1 t1b
    WHERE t1a.id = t1b.id
    AND t2.lid = t1b.lid
  )
)

It reads in English, informally:

Get me all the elements in table1 for which there is no element in table2, which doesn't match such an element from table1

Or also:

Get me the elements from table1, which match all the elements in table2

Thanks for the explanation too. It helps me understand the situation. — StevenG, Sep 20 '17 at 12:43

score 1 · Answer 2 · answered Sep 20 '17 at 12:29

1

That's one of the solutions:

select distinct id from table1 AS T1
where not exists(
    select lid from table2
    except 
    select lid from table1 where id = T1.id
)

answered Sep 20 '17 at 12:29

Michał Turczyn

32,028
14
47
69

SQL Server query with intersect except or union relational Algebra

2 Answers2