[Oracle SQL] SELECT 실습_ 리스트 연산자

[Oracle SQL] SELECT 실습_ 리스트 연산자

I 리스트 연산자 : IN

▶ 숫자, 문자, 날짜, 다중 컬럼 리스트

1. 숫자

1) IN (   ,   )

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE DEPTNO IN (10,20);

---

2) OR 와 동일한 SQL

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE DEPTNO=10 OR DEPTNO=20;

---

2. 문자

1) 데이터는 대소문자 구분

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE JOB IN ('salesman','manager');

---

2) 데이터는 대소문자 구분

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE JOB IN ('SALESMAN','MANAGER');

---

3. 날짜

---

SELECT DEPTNO,EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE IN ('81/01/20','81/05/01');

---

날짜는 암시적 형변환(conversion) 발생

4. 다중 컬럼 리스트

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE (DEPTNO,JOB) IN ((20,'CLERK'),(20,'MANAGER'));

---

5. Sub Query 활용한 리스트 연산자

---

SELECT DEPTNO,EMPNO,ENAME,JOB FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC IN ('NEWYORK','CHICAGO'));

---