software

掌握 JOIN、子查询和查询技术

发布: (2025年12月21日 GMT+8 05:22)
9 min read
原文: Dev.to

Source: Dev.to

请提供您希望翻译的完整文本内容，我将按照要求保留源链接、格式和代码块，仅翻译正文部分。

Source: …

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

-- Verifica se existe ao menos um funcionário com salário > 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
);
-- Departamentos que **não** possuem funcionários
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
);

Source: …

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

使用这些模式编写更易读、高效且易于维护的查询。

SQL Queries & Best‑Practice Tips

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. 索引建议

-- Índices nas colunas usadas em JOINs e WHERE
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_loc ON departments(location_id);

5. 简单连接的解释计划

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. 子查询与连接性能

情况查询备注
较差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 与 IN（大数据集）

-- Menos eficiente
SELECT * FROM employees e
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);
-- Mais eficiente
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
      AND d.location_id = 1700
);

相关子查询与使用派生表的连接

-- Menos eficiente (correlacionada)
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;
-- Mais eficiente (join)
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,
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. 现代语法；多个 join。
  • 子查询 – 标量、返回多行、相关子查询、在 FROM 中使用。
  • 运算符 – EXISTS、ANY、ALL。
  • CTE（公用表表达式） – 简单、链式和递归。
  • 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 合并 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%...