[SQL] ANSI 조인

ANSI조인은 모든 DBMS에서 사용 가능한 조인을 의미한다. 여러 테이블을 연결하는 조인 조건을 WHERE 절에 명시하지 않고 다른 방법을 통하여 기술한다. 따라서 조인 조건과 검색 조건을 분리하여 지정하기 때문에 가독성이 향상된다.

Nature 조인

• 두 개의 테이블에서 공통 컬럼을 찾아서 조인하는 방식
• 반드시 한 개의 공통 컬럼이 있어야 하며, 두 개 이상의 공통 컬럼이 있다면 공통 컬럼들 값이 모두 같은 것만 조회
• 공통 컬럼을 사용할 때 별칭(alias)은 사용 불가능

-- employees 테이블과 departments 테이블의 공통 컬럼 조회
SELECT last_name 이름,department_name 부서명, department_id 부서번호
FROM employees NATURAL JOIN departments;

employees 테이블과 departments 테이블의 공통 컬럼을 자동으로 찾아서 조인을 실행한다. 여기서 employees 테이블과 departments 테이블 간 공통 컬럼이 department_id 뿐만 아니라 manager_id 두 개가 존재하기 때문에 두 개의 컬럼이 모두 일치하는 데이터가 출력된다.

👉 특정 컬럼만 조인하고 싶다면 USING절 사용

 

 

USING(컬럼) 절

• USING 절은 명시적으로 어떤 컬럼을 조인할 지 지정할 수 있다.
• 공통 컬럼을 사용할 때 별칭(alias)은 사용 불가능

SELECT last_name 이름,department_name 부서명, department_id 부서번호
FROM employees JOIN departments USING(department_id);

 

ON 절

• Non-Equi 조인이나 임의의 연산자로 조인할 경우에는 ON절을 사용
• 공통 컬럼은 별칭.컬럼 형식으로 사용

-- 사원 테이블과 부서 테이블을 ON 절을 이용하여 조인  
SELECT e.department_id 부서번호  
FROM employees e JOIN departments d ON e.departmen_id = d.department_id;

-- 부서번호가 90인 사원만 검색하는 조건이 추가  
SELECT e.department_id 부서번호  
FROM employees e INNER JOIN departments d ON e.department_id = d.department_id -- 조인 조건  
WHERE e.department_id=90; -- 검색 조건

-- between a and b 연산자 이용  
SELECT last_name, salary, grade_level  
FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

-- 사원과 담당 관리자의 이름을 출력 (self 조인)  
SELECT e.last_name 사원명, m.last_name 관리자  
FROM employees e JOIN employees m ON e.manager_id = m.employee_id;

 

LEFT OUTER | RIGHT OUTER | FULL OUTER 조인

• LEFT OUTER JOIN:LEFT로 지정된 테이블1의 데이터를 테이블2의 조인 조건 일치여부와 상관없이 모두출력
• RIGHT OUTER JOIN:RIGHT로 지정된 테이블2의 데이터를 테이블1의 조인 조건 일치여부와 상관없이 모두출력
• FULL OUTER JOIN: 양쪽 테이블1과 테이블2의 데이터를 조인 조건 일치 여부와 상관없이 모두 출력

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 LEFT|RIGHT|FULL OUTER JOIN 테이블2 
ON 조인조건 | USING(컬럼)
[WHERE 검색조건];

--  관리자가 없는 사원 정보도 모두 포함하여 조회
SELECT e.last_name 사원명, m.last_name 관리자명 
FROM employees e LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id;

-- 담당하는 사원이 없는 관리자 정보도 모두 포함하여 조회
SELECT e.last_name 사원명, m.last_name 관리자명 
FROM employees e RIGHT OUTER JOIN employees m
ON e.manager_id = m.employee_id;

-- LEFT OUTER 조인, RIGHT OUTER 조인 결과 모두 포함하여 조회
SELECT e.last_name 사원명, m.last_name 관리자명 
FROM employees e FULL OUTER JOIN employees m
ON e.manager_id = m.employee_id;