Clarification on this part in SQL cookbook about the WHERE IN/EXCEPT clause

Question

After checking out some answers on StackOverflow, it seems that this would not work on SQL Server. But that usually users defer to EXISTS.

The examples I've found are all for subqueries (correlated ones). Whereas this is just an intersect statement. So, I'm hoping someone can show me how to implement multiple columns using a regular subquery. Smething like this:

    select *
    from person.person 
    where EXISTS (
        select *
        from (
        (
            select top 10 businessEntityid
                from v
            INTERSECT  
            select businessEntityid
                from Person.Person
        ) 
    ) as a 
    
    )


-

    select *
    from person.person 
    where  EXISTS (
    select * from (
        (
            select top 10 businessEntityid, firstname, lastname
                from v
            INTERSECT  
            select businessEntityid, firstname, lastname
                from Person.Person
        ) 
    ) as a 
    
    )

although, this doesn't seem to work - the sub query returns only 10 different businessids, while the overall query returns everything - almost as though the subquery isn't even used.

So an explanation of EXISTS would be great here.

Thanks

Why a `TOP` without an `ORDER BY`? That means the data engine is free to return 10 arbitrary rows, and those rows could be different *every time* you run the query. — Thom A, Dec 11 '21 at 21:33
Where are you seeing I downvoted? Downvotes are anonymous, so if you *can* see them you should not be sharing said information; however the information you are reading is wrong, I haven't voted here. — Thom A, Dec 11 '21 at 21:43
It's a bit surprising to see an EXISTS combined with an INTERSECT. It seems kinda pointless. INTERSECT can be used to find combinations that are in both. While EXISTS can be used to filter the outside table on combinations where at least 1 can be found in the other. — LukStorms, Dec 11 '21 at 21:44
@LukStorms thanks for taking the time...the book starts with a WHERE IN that has multiple columns...that doesnt work on sql server, and the only alternative ive found online somewhat mimic this is using EXISTS - so im just trying to adapt that to the book problem — avnav99, Dec 11 '21 at 21:46
I think you just need to see some examples of an `EXISTS` and a `NOT EXISTS` — LukStorms, Dec 11 '21 at 21:49
Yes content. Your question is content on Stack Overflow. The votes *rate* that content. That's why the tooltip tells you what the votes mean: the the question does/doesn't research effort; it is/isn't helpful or useful. As someone with almost 400 reputation, you should be aware of how the site works in regards to it's content rating mechanisms now. — Thom A, Dec 11 '21 at 21:50
@LukStorms i mean im just trying to recreate the WHERE IN solution in the book into a sql server solution... — avnav99, Dec 11 '21 at 21:50
This isn't a "help site" per se, and for many, this can lead to frustration due to their not fully understanding this key concept. It is a question and answer site where both questions and answer quality are curated by site users with the goal of creating a collection of high-quality questions and their answers. Help is often obtained, but as a useful by-product, not as a primary goal. Comments are there to give feedback and help posters create better questions, questions that we hope will be judged good enough to keep and be answered. Votes, both up and down, are encouraged and expected. — Hovercraft Full Of Eels, Dec 11 '21 at 21:51
I agree with @LukStorms here. An `EXISTS` with an `INTERSECT` is odd; a `NOT EXISTS` would seem like a better choice. Some sample data and expected results would likely help us understand you actual goal here. — Thom A, Dec 11 '21 at 21:52
I still thinking about what this solution is about. Regardless this is not a common situation at all. — shawnt00, Dec 11 '21 at 21:52
This is a Q&A site, not an educational system, @avnav99... Though we're off topic here. Perhaps you would be better asking about your misunderstanding about the site's content rating on [meta]; there you can be given a detailed answer. — Thom A, Dec 11 '21 at 21:52
In answer to your comment [here](https://stackoverflow.com/questions/70319194/can-someone-elaborate-on-this-part-in-sql-cookbook-about-the-where-in-except-cla#comment124304313_70319194) it would certainly help give you *consistent* results. But, as mentioned, likely you shouldn't be using the query like that at all. — Thom A, Dec 11 '21 at 21:56
If subquery has rows then the `exists` is true. The random `top 10` will definitely interfere with the intersection. — shawnt00, Dec 11 '21 at 22:00
@shawnt00 ive changed it to ```select top 10 businessEntityid, firstname, lastname from v where LastName = 'Johnson'``` so im not sure it was that. im just using adventure works where v is a view of exactly everything in person.person — avnav99, Dec 11 '21 at 22:03
This question reminded me that I had forgotten to include tupple compares in [this silly answer](https://stackoverflow.com/a/70198512/4003419). — LukStorms, Dec 11 '21 at 22:05
@shawnt00 also want to mention that doesnt make sense to me, because if i used ```order by newid()``` to pull random rows as a test -- unless this is not comprable? — avnav99, Dec 11 '21 at 22:30
I'm not sure what's you intend to test by only pulling ten rows. The subquery results still depends on the intersection. The query output could change every time you run it. — shawnt00, Dec 12 '21 at 02:23

David Browne - Microsoft · Answer 1 · 2021-12-11T22:03:56.257

2

This query

select empno,ename,job,sal,deptno
  from emp
where (ename,job,sal) in (
  select ename,job,sal from emp
 intersect
  select ename,job,sal from V
 )

is equivalent to

select empno,ename,job,sal,deptno
  from emp
where (ename,job,sal) in ( select ename,job,sal from V )

which, with EXISTS would be

select empno,ename,job,sal,deptno
  from emp e
where exists (
  select *
  from V
  where ename = e.ename
    and job = e.job
    and sal = e.sal
 )

edited Dec 11 '21 at 22:03

answered Dec 11 '21 at 21:59

David Browne - Microsoft

80,331
6
39
67

thanks for taking the time man, i mean im not the one to say if its dumb or not it came from a pretty reputable book called sql cookbook...i think its an example written to emphasize INTERSECT - and because the example doesnt work on sql server im just trying to adapt it to that... – avnav99 Dec 11 '21 at 22:00
But INTERSECT is not needed in the query. – David Browne - Microsoft Dec 11 '21 at 22:01
I'd personally say it's a bad example then @avnav99 . There are certainly use cases for using `INTERSECT` but this feels a little like taking 3 left turns to turn right, when turning right was an option. It works, but it was the long and unobovious route. – Thom A Dec 11 '21 at 22:03
@Larnu fair enough, thanks for the time and input i appreciate it – avnav99 Dec 11 '21 at 22:04
@DavidBrowne-Microsoft just so im getting this 100% because i mentioned this in the question - can i assume that the equivalent to oracle's ability to do multi-column IN statements in sql server will always entail a correlated subquery? – avnav99 Dec 11 '21 at 22:17
You do realise that oracle also has EXISTS, right. It's standard ANSI SQL. The tupple compare, or the intersect are rarely used actually. – LukStorms Dec 11 '21 at 22:21
@LukStorms not sure if you're responding to me... but in case you are...im saying oracle can do what sql server does (EXISTS) and for sql server to do something that mimics what oracle does (multi column WHERE IN) it can only use EXISTS **with a correlated subquery...right? – avnav99 Dec 11 '21 at 22:26
1

Well... wouldn't say it's the only way. There's bound to be other ways. F.e. `where concat(colA, colB) in (select concat(colX, colY) from table2)` But realise that EXISTS can do more than tupple compare. Because it's not just restricted to equality `=`. – LukStorms Dec 11 '21 at 22:35

Charlieface · Accepted Answer · 2021-12-12T01:23:44.877

The queries that you are attempting don't make much sense. I have no idea why you have top there, nor why you have swapped the order of the intersect tables

After cleaning up the query from the Cookbook, you have this

select
  emp.no,
  emp.name,
  emp.job,
  emp.salary,
  emp.deptno
from emp
where (emp.name, emp.job, emp.salary) in (
    select
      emp.name, emp.job, emp.salary
    from emp

    intersect

    select
      v.name, v.job, v.salary
    from v
)

This can be trivially transformed to an EXISTS, by moving the comparison to the inside of the subquery. You can either do this as another INTERSECT

where exists (
    select
      emp.name, emp.job, emp.salary

    intersect

    select
      emp2.name, emp2.job, emp2.salary
    from emp2

    intersect

    select
      v.name, v.job, v.salary
    from v
)

or by using three = equality predicates

where exists (
    select
      emp2.name, emp2.job, emp2.salary
    from emp2
    where emp2.name = emp.name
      and emp2.job = emp.job
      and emp2.salary = emp.salary

    intersect

    select
      v.name, v.job, v.salary
    from v
)

There is actually a difference between those two, in how they handle nulls, but the result is the same on non-nullable columns.

However, there is what seems a much simpler method to this. The query just seems to be asking for matching results from v, so you can just remove the extra query on emp

where exists (
    select
      emp.name, emp.job, emp.salary

    intersect

    select
      v.name, v.job, v.salary
    from v
)

or with =

where exists (select 1
    from v
    where v.name = emp.name
      and v.job = emp.job
      and v.salary = emp.salary
)

This last version is how it is normally written for SQL Server.

ur a legend man. i was just trying to adapt it to adventureworks that comes with sql server as a toy database. thanks so much for this — avnav99, Dec 12 '21 at 01:20

Clarification on this part in SQL cookbook about the WHERE IN/EXCEPT clause

2 Answers2