software

JOINs와 Subqueries, 쿼리 기술 마스터하기

발행: (2025년 12월 21일 오전 06:22 GMT+9)
9 min read
원문: Dev.to

Source: Dev.to

번역을 진행하려면 번역이 필요한 전체 텍스트를 제공해 주세요. 텍스트를 주시면 요청하신 대로 한국어로 번역해 드리겠습니다.

1. INNER JOIN

INNER JOIN은 양쪽 테이블에 모두 일치하는 행만 반환합니다. 가장 일반적인 JOIN 유형이며 테이블 간 관계의 기본입니다.

SELECT colunas
FROM   tabela1
INNER JOIN tabela2
        ON tabela1.coluna = tabela2.coluna;

예제 1 – 직원과 부서 연결

SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM   employees   e
INNER JOIN departments d
        ON e.department_id = d.department_id;

예제 2 – 여러 조건을 가진 INNER JOIN

SELECT 
    o.order_id,
    c.customer_name,
    p.product_name
FROM   orders        o
INNER JOIN customers   c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products    p ON oi.product_id = p.product_id
WHERE  o.order_date >= DATE '2024-01-01';

구식 구문 vs. ANSI 구문 (권장)

-- Sintaxe antiga (não recomendada)
SELECT e.first_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

-- Sintaxe moderna (recomendada)
SELECT e.first_name, d.department_name
FROM   employees e
INNER JOIN departments d
        ON e.department_id = d.department_id;

컬럼 포맷팅

SELECT 
    UPPER(e.last_name)               AS sobrenome,
    d.department_name                AS departamento,
    TO_CHAR(e.salary, 'L99G999D99')  AS salario_formatado
FROM   employees   e
INNER JOIN departments d
        ON e.department_id = d.department_id
WHERE  e.salary > 5000
ORDER BY e.salary DESC;

2. 서브쿼리

서브쿼리는 다른 쿼리 안에 중첩된 쿼리로, 복잡한 문제를 더 작은 부분으로 나눌 수 있게 합니다.

단순 서브쿼리

SELECT employee_id, first_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

IN을 사용한 서브쿼리

SELECT employee_id, first_name, department_id
FROM   employees
WHERE  department_id IN (
    SELECT department_id 
    FROM   departments 
    WHERE  location_id = 1700
);

상관 서브쿼리

SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM   employees e
WHERE  e.salary > (
    SELECT AVG(e2.salary)
    FROM   employees e2
    WHERE  e2.department_id = e.department_id
);

FROM 절의 서브쿼리 (파생 테이블)

SELECT dept_avg.department_id,
       dept_avg.media_salarial,
       d.department_name
FROM (
    SELECT department_id,
           AVG(salary) AS media_salarial
    FROM   employees
    GROUP BY department_id
) dept_avg
INNER JOIN departments d
        ON dept_avg.department_id = d.department_id
WHERE  dept_avg.media_salarial > 8000;

추가 계산이 포함된 서브쿼리

SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    (SELECT AVG(salary) FROM employees)               AS media_geral,
    e.salary - (SELECT AVG(salary) FROM employees)    AS diferenca_media
FROM   employees e;

3. 연산자 EXISTS / NOT EXISTS

-- 급여가 10,000 초과인 직원이 최소 한 명 있는지 확인
SELECT d.department_name
FROM   departments d
WHERE  EXISTS (
    SELECT 1
    FROM   employees e
    WHERE  e.department_id = d.department_id
      AND  e.salary > 10000
);
-- 직원이 **없는** 부서
SELECT d.department_name
FROM   departments d
WHERE  NOT EXISTS (
    SELECT 1
    FROM   employees e
    WHERE  e.department_id = d.department_id
);

4. 연산자 ANY와 ALL

SELECT employee_id, first_name, salary
FROM   employees
WHERE  salary > ANY (
    SELECT salary
    FROM   employees
    WHERE  department_id = 50
);
SELECT employee_id, first_name, salary
FROM   employees
WHERE  salary > ALL (
    SELECT salary
    FROM   employees
    WHERE  department_id = 50
);

5. 고급 조인과 서브쿼리 및 CTE

예시: 부서 평균 급여와 비교

SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary,
    dept_stats.media_dept,
    dept_stats.max_dept
FROM   employees e
INNER JOIN departments d
        ON e.department_id = d.department_id
INNER JOIN (
    SELECT department_id,
           AVG(salary) AS media_dept,
           MAX(salary) AS max_dept
    FROM   employees
    GROUP BY department_id
) dept_stats
        ON e.department_id = dept_stats.department_id
WHERE  e.salary > dept_stats.media_dept * 1.2;

예시: 위치 평균 급여와 비교

SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    l.city
FROM   employees   e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations   l ON d.location_id = l.location_id
WHERE  e.salary > (
    SELECT AVG(e2.salary)
    FROM   employees e2
    INNER JOIN departments d2 ON e2.department_id = d2.department_id
    WHERE  d2.location_id = l.location_id
);

6. 공통 테이블 식 (CTEs)

간단한 CTE

WITH dept_salaries AS (
    SELECT department_id,
           AVG(salary)   AS avg_salary,
           COUNT(*)      AS num_employees
    FROM   employees
    GROUP BY department_id
)
SELECT d.department_name,
       ds.avg_salary,
       ds.num_employees
FROM   dept_salaries ds
INNER JOIN departments d
        ON ds.department_id = d.department_id
WHERE  ds.avg_salary > 7000;

중첩 CTE

WITH high_earners AS (
    SELECT employee_id, first_name, salary, department_id
    FROM   employees
    WHERE  salary > 10000
),
dept_info AS (
    SELECT department_id, department_name, location_id
    FROM   departments
)
SELECT he.first_name,
       he.salary,
       di.department_name
FROM   high_earners he
INNER JOIN dept_info di
        ON he.department_id = di.department_id
ORDER BY he.salary DESC;

재귀 CTE – 직원 계층 구조

WITH RECURSIVE employee_hierarchy (
    employee_id,
    first_name,
    manager_id,
    level_num
) AS (
    -- Anchor member
    SELECT employee_id,
           first_name,
           manager_id,
           1 AS level_num
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.employee_id,
           e.first_name,
           e.manager_id,
           eh.level_num + 1
    FROM   employees e
    INNER JOIN employee_hierarchy eh
            ON e.manager_id = eh.employee_id
)
SELECT LPAD(' ', (level_num - 1) * 2) || first_name AS hierarchy,
       level_num
FROM   employee_hierarchy
ORDER BY level_num, first_name;

7. LATERAL (CROSS APPLY)

LATERAL 연산자는 오른쪽 서브쿼리가 왼쪽 테이블의 컬럼을 참조할 수 있게 합니다.

SELECT 
    d.department_name,
    top_earners.*
FROM   departments d
CROSS APPLY (
    SELECT employee_id,
           first_name,
           salary
    FROM   employees e
    WHERE  e.department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) top_earners;

결론

이 가이드는 Oracle Database에서 고급 쿼리 기술을 주요하게 모아, 실용적인 예제를 제공합니다:

  • INNER JOIN (단일 및 다중)
  • 서브쿼리 (단일, 상관, FROM에서)
  • 연산자 EXISTS, ANY, ALL
  • CTE (단일, 체인 및 재귀)
  • LATERAL/CROSS APPLY

이 패턴들을 활용하여 더 읽기 쉽고, 효율적이며 유지보수가 쉬운 쿼리를 작성하십시오.

Source: …

SQL 쿼리 및 모범 사례 팁

1. 부서별 최고 급여자

SELECT *
FROM (
    SELECT e.employee_id,
           e.first_name,
           e.salary,
           ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn
    FROM employees e
    WHERE e.department_id = d.department_id
) top_earners
WHERE rn  (SELECT AVG(salary) FROM employees) THEN 'Acima da Média'
           WHEN salary = (SELECT AVG(salary) FROM employees) THEN 'Na Média'
           ELSE 'Abaixo da Média'
       END AS classificacao_salarial
FROM employees;

3. 관리자 이름과 평균 이상 급여를 가진 직원

SELECT e1.employee_id   AS funcionario_id,
       e1.first_name    AS funcionario_nome,
       e2.first_name    AS gerente_nome,
       e1.salary
FROM employees e1
LEFT JOIN employees e2
       ON e1.manager_id = e2.employee_id
WHERE e1.salary > (
    SELECT AVG(salary)
    FROM employees e3
    WHERE e3.manager_id = e1.manager_id
);

4. 인덱스 권장 사항

-- JOIN 및 WHERE 절에 사용되는 컬럼에 인덱스 생성
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_loc ON departments(location_id);

5. 간단한 조인에 대한 Explain plan

EXPLAIN PLAN FOR
SELECT e.first_name,
       d.department_name
FROM employees   e
INNER JOIN departments d
        ON e.department_id = d.department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

6. 서브쿼리 vs. 조인 성능

케이스쿼리비고
비효율sql SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments); IN과 서브쿼리를 사용함.
효율sql SELECT e.* FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; 직접 조인 사용.

EXISTS vs. IN (대용량 데이터셋)

-- 덜 효율적
SELECT * FROM employees e
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
-- 더 효율적
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
      AND d.location_id = 1700
);

상관 서브쿼리 vs. 파생 테이블 조인

-- 덜 효율적 (상관 서브쿼리)
SELECT e.employee_id,
       e.salary,
       (SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department_id = e.department_id) AS avg_dept_salary
FROM employees e;
-- 더 효율적 (조인)
SELECT e.employee_id,
       e.salary,
       dept_avg.avg_salary
FROM employees e
INNER JOIN (
    SELECT department_id,
           AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg
   ON e.department_id = dept_avg.department_id;

7. 분석 예시 – 월별 매출 및 최고 제품

WITH monthly_sales AS (
    SELECT TRUNC(order_date, 'MM') AS month,
           SUM(total_amount)       AS total_sales,
           COUNT(DISTINCT customer_id) AS unique_customers
    FROM orders
    WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY TRUNC(order_date, 'MM')
),
product_performance AS (
    SELECT p.product_id,
           p.product_name,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products      p
    INNER JOIN order_items oi ON p.product_id = oi.product_id
    INNER JOIN orders      o  ON oi.order_id = o.order_id
    WHERE o.order_date >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY p.product_id, p.product_name
)
SELECT ms.month,
       ms.total_sales,
       ms.unique_customers,
       pp.product_name AS top_product,
       pp.revenue      AS top_product_revenue
FROM monthly_sales ms
CROSS JOIN LATERAL (
    SELECT product_name, revenue
    FROM product_performance
    ORDER BY revenue DESC
    FETCH FIRST 1 ROW ONLY
) pp
ORDER BY ms.month DESC;

8. 재귀 계층 구조 (직원)

WITH RECURSIVE emp_hierarchy AS (
    SELECT employee_id,
          ```

```sql
WITH emp_hierarchy AS (
    SELECT employee_id,
           first_name,
           manager_id,
           salary,
           1 AS level_depth,
           CAST(first_name AS VARCHAR2(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id,
           e.first_name,
           e.manager_id,
           e.salary,
           eh.level_depth + 1,
           eh.path || ' > ' || e.first_name
    FROM employees e
    INNER JOIN emp_hierarchy eh
            ON e.manager_id = eh.employee_id
)
SELECT eh.path,
       eh.salary,
       (SELECT AVG(salary) FROM employees) AS avg_company_salary,
       (SELECT AVG(salary)
        FROM employees e
        WHERE e.manager_id = eh.manager_id) AS avg_peer_salary,
       CASE 
           WHEN eh.salary > (SELECT AVG(salary)
                             FROM employees e
                             WHERE e.manager_id = eh.manager_id)
           THEN 'Acima dos Pares'
           ELSE 'Abaixo dos Pares'
       END AS comparacao
FROM emp_hierarchy eh
ORDER BY eh.level_depth, eh.first_name;

가이드 요약

  • INNER JOINs – 테이블 간 관계; 전통적인 구문 vs. 현대적인 구문; 다중 조인.
  • Subqueries – 스칼라, 다중 행, 상관 서브쿼리, FROM 절에서.
  • Operadores – EXISTS, ANY, ALL.
  • CTEs – 단순, 연쇄 및 재귀.
  • LATERAL / CROSS APPLY – 외부 테이블을 참조하는 서브쿼리.

이러한 기능을 활용하여 SQL 쿼리를 최적화하고 정리하세요.

Back to Blog

관련 글

더 보기 »

blob1

sql WITH VersionsToAscii AS SELECT t.INTERNIDENTITY, t.INTERNINSTID, t.INTERNVERSION, t.INTERNTIMESTAMP, t.IOIID, RTRIM XMLAGG XMLELEMENTE, c.chunk_content ORDE...

SQL10

SQL 설정 SET VERIFY OFF FEEDBACK OFF HEADING OFF ECHO ON SET SERVEROUTPUT ON -- 인수를 캡처하거나 기본값으로 빈 문자열 사용 프롬프트 없음 COLUMN p_flag_col NEW_VA...

Oracle MERGE INTO

Forem 로고 https://media2.dev.to/dynamic/image/width=65,height=,fit=scale-down,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%...