[SQL Server][Oracle][MySQL][PostgreSQL] IN條件式寫法的差異

        一般找某個資料表的資料是否存在另一個資料表中，就語法上來說，可以用INNER JOIN，若條件式僅JOIN單一欄位，可以改成用IN或EXISTS；若是JOIN多個欄位，就無法用IN囉，這是在SQL Server的情況

        無意間看到在Oracle中，IN條件式的寫法裡竟然可以接受多個欄位呢，另外試了MySQL與PostgreSQL也都可以喔

[Oracle]

alter session set current_schema=SCOTT;

DROP TABLE emp2 PURGE;

create table EMP2 as

select ENAME, job, SAL from EMP where job = 'CLERK';

select * from emp2;

--EXISTS的寫法 

SELECT empno, ename, job, sal, deptno

      FROM emp a

     WHERE EXISTS (SELECT NULL

              FROM emp2 b

             WHERE b.ename = a.ename

               AND b.job = a.job

               and B.SAL = a.SAL);

--IN的寫法 

SELECT empno, ename, job, sal, deptno

      from EMP

     where (ENAME, job, SAL) in (select ENAME, job, SAL from EMP2);

[SQL Server]

drop table EMP2;

drop table EMP;

CREATE TABLE EMP2

     (ENAME VARCHAR(10),

     JOB VARCHAR(9),

     SAL numeric(7,2)

);

create table EMP

  (EMPNO numeric(4,0),

  ENAME VARCHAR(10),

  JOB varchar(9),

  SAL numeric(7,2),

  DEPTNO numeric(2,0)

 );

Insert into EMP2 values ('SMITH','CLERK',800);

Insert into EMP2 values ('ADAMS','CLERK',1100);

insert into EMP2 values ('JAMES','CLERK',950);

insert into EMP2 values ('MILLER','CLERK',1300);

Insert into EMP values (7369,'SMITH','CLERK',800,20);

Insert into EMP values (7876,'ADAMS','CLERK',1100,20);

insert into EMP values (7900,'JAMES','CLERK',950,30);

insert into EMP values (7934,'MILLER','CLERK',1300,10);
insert into EMP values (8000,'TERRY','CLERK',1250,10);

--EXISTS的寫法  

SELECT empno, ename, job, sal, deptno

      FROM emp a

     WHERE EXISTS (SELECT NULL

              FROM emp2 b

             WHERE b.ename = a.ename

               AND b.job = a.job

               and B.SAL = a.SAL);

--IN的寫法(多欄位會報錯，單一欄位是OK的)
SELECT empno, ename, job, sal, deptno

      from EMP

     where (ENAME, job, SAL) in (select ENAME, job, SAL from EMP2);

[MySQL]

可直接用SQL Server的範例語法

[PostgreSQL]

可直接用SQL Server的範例語法

        姑且不論效能差異，當大家都可以就SQL Server不能這樣用，那你要不要改善一下！