Geek asks and answers

How to use "IN" for multiple columns

This question may be trivial or even stupid, but I was wondering if there is a way to use "IN" in more than one column by one match. For example, I use

select emp_id from employee where emp_id IN (select emp_id from employee_other)

How could I achieve something like

select emp_id from employee where emp_id,emp_org IN (select emp_id,emp_org from employee_other)

I know that I can’t use the following, because it will just do the union, while I need a choice based on matching one to one record.

select emp_id from employee where emp_id IN (select emp_id from employee_other) and emp_org in (select emp_org from employee)

Please note that I do NOT want to use EXCEPT.

Thanks guys,

+3
source share
5 answers

You can use the EXISTS statement

select e.emp_id 
from employee e
where EXISTS
    (
    SELECT *
    FROM employee_other eo
    WHERE e.emp_id = eo.emp_id
        AND e.emp_org = eo.emp_org
    )
+5
source

IN Microsoft SQL Server , .. X IN (...), X,Y IN (...).

, :

  • EXISTS

, :

select emp_id
from employee
    inner join (select emp_id,emp_org from employee) as x
    on employee.emp_id = x.emp_id and employee.emp_org = x.emp_org

, , .

EXISTS, :

select emp_id
from employee
where exists (
    select emp_id,emp_org from employee e2
    where e2.emp_id = employee.emp_id and e2.emp_org = employee.emp_org)

, , "", , "" , EXISTS .

+3

select e1.emp_id from employee  e1
inner join employee_other e2 on e1.emp_id = e1.emp_id and e1.emp_org = e2.emp_org

, Distinct, employee_other .

select Distinct e1.emp_id from employee  e1
inner join employee_other e2 on e1.emp_id = e1.emp_id and e1.emp_org = e2.emp_org
-1

, emp_org in (select emp_org from employee), ?

?

select emp_id from employee e
where exists (select 1 from employee_other eo  
              WHERE e.emp_id =eo.emp_id and 
              AND e.emp_org = eo.emp_org )
-1

You had it almost completely in your second example. You just need to add parens around the column names.

select emp_id 
from employee 
where (emp_id,emp_org) IN (select emp_id,emp_org from employee)
-1
source

More articles:


All Articles