[오라클]select문의 join에 대하여...

오라클에서 JOIN은 둘 이상의 테이블을 연결하여 관련된 데이터를 함께 조회하는 기능입니다. 

JOIN의 이론과 예제를 함께 살펴보겠습니다.

JOIN은 보통 다음과 같은 형식으로 작성됩니다:

SELECT 열1, 열2, ...
FROM 테이블1
JOIN 테이블2 ON 조인조건;

열1, 열2, ...: 조회할 열(필드)의 이름을 나타냅니다. 필요에 따라 테이블명과 열명을 지정할 수 있습니다.
테이블1, 테이블2: JOIN할 테이블의 이름을 나타냅니다.
ON 조인조건: JOIN할 테이블 간의 연결 조건을 지정합니다. 일치하는 행(레코드)을 찾기 위해 사용됩니다.

이제 몇 가지 JOIN 예제를 살펴보겠습니다.
INNER JOIN(내부 조인) 예제:

--ANSI VERSION
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
INNER JOIN departments 
    ON employees.dept_id = departments.dept_id;

--ORACLE VERSION
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees, departments 
WHERE employees.dept_id = departments.dept_id;

위의 예제는 "employees" 테이블과 "departments" 테이블을 "dept_id" 열을 기준으로 INNER JOIN하여 "emp_id", "emp_name", "dept_name" 열을 함께 조회합니다.

LEFT JOIN(왼쪽 조인) 예제:

-- ANSI VERSION
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
LEFT JOIN departments
    ON employees.dept_id = departments.dept_id;
    
--ORACLE VERSION
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees, departments
WHERE employees.dept_id = departments.dept_id(+);

위의 예제는 "employees" 테이블의 모든 행을 포함하고, "departments" 테이블과 "dept_id" 열을 기준으로 LEFT JOIN하여 "emp_id", "emp_name", "dept_name" 열을 함께 조회합니다. "departments" 테이블에서 일치하는 행이 없는 경우에도 "employees" 테이블의 데이터는 모두 반환됩니다.

RIGHT JOIN(오른쪽 조인) 예제:

--ANSI VERSION    
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
RIGHT JOIN departments 
    ON employees.dept_id = departments.dept_id;
    
--ORACLE VERSION    
SELECT 
    employees.emp_id, employees.emp_name, departments.dept_name
FROM employees, departments 
WHERE employees.dept_id(+) = departments.dept_id;

위의 예제는 "departments" 테이블의 모든 행을 포함하고, "employees" 테이블과 "dept_id" 열을 기준으로 RIGHT JOIN하여 "emp_id", "emp_name", "dept_name" 열을 함께 조회합니다. "employees" 테이블에서 일치하는 행이 없는 경우에도 "departments" 테이블의 데이터는 모두 반환됩니다.

JOIN은 둘 이상의 테이블을 연결하여 관련된 데이터를 함께 조회하는 강력한 기능입니다. INNER JOIN, LEFT JOIN, RIGHT JOIN 등 다양한 JOIN 유형을 사용하여 필요한 데이터를 추출할 수 있습니다. JOIN의 조인조건을 적절히 설정하여 원하는 결과를 얻을 수 있습니다.