[Database] SQLD 자격검정 실전문제 개념 정리 3편(p50-p67)

이번 포스팅에서는 이전 포스팅에 이어서 SQL 자격검정 실전문제 책을 보며 익힌 내용들을 정리하는 시간을 가져보도록 하겠습니다

EQUI JOIN이란?

EQUI JOIN은 SQL에서 두 테이블을 특정 열의 값이 동일한 행들로 연결하는 조인 방식입니다. EQUI JOIN은 등가 비교 연산자 =를 사용하여 두 테이블 간의 열을 비교합니다. EQUI JOIN은 INNER JOIN과 매우 유사하며, 사실상 INNER JOIN의 일종입니다. EQUI JOIN은 두 테이블 간의 공통 열을 기준으로 데이터를 결합합니다.

EQUI JOIN 예제 코드

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees, Departments
WHERE Employees.DepartmentID = Departments.DepartmentID;

INNER JOIN 예제 코드

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

NON EQUI JOIN이란?

Non-Equi Join은 두 테이블 간의 조인 조건으로 등가 비교(=) 연산자가 아닌 다른 비교 연산자(예: <, >, <=, >=, !=)를 사용하는 조인입니다. Non-Equi Join은 특정한 값 범위나 다른 조건을 만족하는 행을 결합할 때 사용됩니다.

대부분 Non-Equi Join을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있습니다.

예제 코드

SELECT Employees.EmployeeID, Employees.EmployeeName, Employees.Salary, SalaryGrades.Grade
FROM Employees
JOIN SalaryGrades ON Employees.Salary BETWEEN SalaryGrades.MinSalary AND SalaryGrades.MaxSalary;

데이터베이스 옵티마이저

SQL 쿼리의 최적화를 담당하는 데이터베이스 옵티마이저는 FROM 절에 나열된 테이블을 두 개씩 짝지어 JOIN합니다.

어떤 테이블을 먼저 JOIN할지는 옵티마이저의 판단에 따라 결정됩니다. 옵티마이저는 다양한 기준(예: 통계 정보, 인덱스 사용 여부, 조인 조건 등)을 기반으로 최적의 조인 순서를 선택합니다.

일반적으로 FROM 절에 나열된 순서대로 조인하는 것이 아니라, 성능이 가장 좋다고 판단되는 순서대로 조인합니다.

순수 관계 연산자

순수 관계 연산자의 종류에는 SELECT, PROJECT, JOIN, DIVIDE가 있습니다.

SELECT (σ): 조건을 만족하는 행을 선택

PROJECT (π): 특정 열만 선택

JOIN (⨝): 두 테이블을 공통된 속성을 기준으로 결합

DIVIDE (÷): 두 번째 관계의 모든 튜플과 연관된 첫 번째 관계의 튜플을 반환

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

• INNER JOIN
• NATURAL JOIN
• USING 조건절
• ON 조건절
• CROSS JOIN
• OUTER JOIN(LEFT, RIGHT, FULL)

NATURAL JOIN은 두 테이블 간의 동일한 이름의 열을 자동으로 사용하여 조인합니다. 동일한 이름의 모든 열을 기준으로 행을 결합합니다. NATURAL JOIN은 EQUI JOIN의 일종으로 볼 수 있습니다.

NOT EXIST 절

NOT EXISTS 구문은 SQL에서 서브쿼리의 결과가 존재하지 않음을 확인하는 데 사용됩니다. 주로 조건에 맞는 행이 존재하지 않을 때, 특정 작업을 수행하고자 할 때 사용됩니다.

구문 형식

SELECT columns
FROM table
WHERE NOT EXISTS (subquery);

예제 코드

SELECT *
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM Departments d
    WHERE e.DepartmentID = d.DepartmentID
);

SELECT 1 FROM Departments d WHERE e.DepartmentID = d.DepartmentID 서브쿼리는 Employees 테이블의 각 행에 대해 실행됩니다.
서브쿼리가 행을 반환하면, 해당 직원은 부서에 속해 있는 것입니다.
서브쿼리가 행을 반환하지 않으면, 해당 직원은 부서에 속해 있지 않은 것입니다.

NOT EXISTS는 서브쿼리가 행을 반환하지 않는 경우를 찾습니다.
즉, Employees 테이블의 행에 대해 서브쿼리가 결과를 반환하지 않는 경우, 그 직원이 결과에 포함됩니다.

Using 조건

USING 조건절은 주로 두 테이블을 조인할 때 사용되며, 동일한 이름을 가진 컬럼을 기준으로 조인할 경우에 유용합니다. USING 조건절을 사용하면 조인할 때 공통된 컬럼 이름을 명시할 수 있으며, 해당 컬럼 이름을 한 번만 적어주면 됩니다. 이는 ON 조건절을 사용하는 것보다 더 간단하고 간결하게 조인 조건을 작성할 수 있게 해줍니다.

예제 코드

SELECT employees.name, departments.department_name
FROM employees
JOIN departments
USING (department_id);

USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없습니다.

UNION, UNION ALL

UNION과 UNION ALL은 SQL에서 두 개 이상의 SELECT 쿼리 결과를 결합하는 데 사용되는 연산자입니다. 두 연산자의 차이점은 결과 집합에서 중복된 행을 처리하는 방식에 있습니다.

UNION 연산자는 결합된 결과 집합에서 중복된 행을 제거합니다. 즉, 각 행이 고유한 결과 집합을 반환합니다. 중복된 행을 제거하는 과정 때문에 성능이 UNION ALL에 비해 느릴 수 있습니다.

반면 UNION ALL 연산자는 결합된 결과 집합에서 중복된 행을 제거하지 않습니다. 모든 행을 그대로 포함하므로, 중복된 행도 결과 집합에 포함됩니다. 중복을 제거하는 추가 작업이 없기 때문에 UNION보다 성능이 더 좋습니다.

예제 코드

SELECT name FROM employees
UNION
SELECT name FROM managers;


SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

(+) 기호?

Oracle에서 (+) 기호는 고전적인 외부 조인을 나타내는 구문입니다. 이는 ANSI SQL 표준의 LEFT OUTER JOIN 또는 RIGHT OUTER JOIN과 같은 역할을 합니다. (+) 기호는 조인 조건에서 특정 테이블의 컬럼 옆에 사용되어 해당 테이블이 조인에서 외부 테이블임을 나타냅니다.

ANSI SQL 표준의 LEFT OUTER JOIN

SELECT e.*, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

Oracle LEFT OUTER JOIN

SELECT e.*, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

Oracle RIGHT OUTER JOIN

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

다중 컬럼(Multi Column) 서브쿼리

서브쿼리의 실행 결과로 여러 컬럼을 반환합니다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있고 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야합니다.

서브 쿼리	사용 위치	설명
스칼라 서브 쿼리	SELECT 절	단일 컬럼, 단일 행을 반환 (즉 단 하나의 값)
인라인 뷰	FROM 절	View와 사용적인 측면에서 동일함 (임시 뷰, 임시 테이블)
중첩 서브 쿼리	WHERE, HAVING	다중 칼럼 또는 다중 행을 반환

CUBE, GROUPING SETS, ROLLUP

CUBE는 지정된 컬럼들의 모든 조합에 대해 집계를 계산합니다. 이는 다차원 데이터 분석에서 유용하며, 가능한 모든 그룹화 조합을 계산합니다.

CUBE 예제 코드

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY CUBE (department_id, job_id);

해당 쿼리는 department_id와 job_id의 모든 조합에 대해, 각각의 그룹별로 직원 수를 계산합니다. 또한, 각 개별 컬럼과 전체 행에 대한 집계도 포함됩니다.

GROUPING SETS는 특정 그룹화 집합을 명시적으로 지정하여 그 집합에 대해서만 집계를 계산합니다. 이는 필요한 그룹화 조합만 선택적으로 계산할 수 있어 효율적입니다.

GROUPING SETS 예제 코드

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
    (department_id),
    (job_id),
    (department_id, job_id),
    ()
);

해당 쿼리는 department_id로만 그룹화, job_id로만 그룹화, department_id와 job_id로 그룹화, 그리고 전체 집합에 대한 그룹화를 수행합니다.

ROLLUP은 지정된 컬럼 순서에 따라 계층적으로 집계를 계산합니다. 이는 계층적인 데이터 집계(예: 연도별, 분기별, 월별 집계 등)에 유용합니다.

ROLLUP 예제 코드

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY ROLLUP (department_id, job_id);

해당 쿼리는 department_id와 job_id의 순서대로 그룹화를 수행합니다. 즉, department_id에 대한 집계, department_id와 job_id의 조합에 대한 집계, 그리고 전체 집합에 대한 집계를 포함합니다.

집합 연산자의 종류

합집합은 UNION, 교집합은 INTERSECT, 차집합은 MINUS/EXCEPT가 있습니다.

집합(SET) 연산자를 사용한 SQL의 ORDER BY 절은 최종 결과를 정렬하며, 가장 마지막 줄에 한번만 사용할 수 있습니다.