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

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

CodePudding user response：

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
 )

CodePudding user response：

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.