Oracle数据库连接查询

mahan

Oracle数据库连接查询详解
1. 内连接 (INNER JOIN)
原理
内连接返回两个表中连接条件完全匹配的行，即只返回两个表中都存在对应记录的数据。

语法
-- 标准语法
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- 传统语法
SELECT columns
FROM table1, table2
WHERE table1.column = table2.column;
案例
-- 创建示例表
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_id NUMBER
);

CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

-- 插入测试数据
INSERT INTO employees VALUES (1, '张三', 10);
INSERT INTO employees VALUES (2, '李四', 20);
INSERT INTO employees VALUES (3, '王五', 10);
INSERT INTO employees VALUES (4, '赵六', NULL);

INSERT INTO departments VALUES (10, '技术部');
INSERT INTO departments VALUES (20, '销售部');
INSERT INTO departments VALUES (30, '人事部');

-- 内连接查询
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：只返回有对应部门的员工
-- 张三 - 技术部
-- 李四 - 销售部
-- 王五 - 技术部
2. 左连接 (LEFT JOIN) / 左外连接 (LEFT OUTER JOIN)
原理
左连接返回左表的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，则返回NULL值。

语法
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
-- 或
SELECT columns
FROM table1
LEFT OUTER JOIN table2 ON table1.column = table2.column;
案例
-- 左连接查询
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：返回所有员工，包括没有部门的员工
-- 张三 - 技术部
-- 李四 - 销售部
-- 王五 - 技术部
-- 赵六 - NULL
3. 右连接 (RIGHT JOIN) / 右外连接 (RIGHT OUTER JOIN)
原理
右连接返回右表的所有记录，以及左表中匹配的记录。如果左表中没有匹配的记录，则返回NULL值。

语法
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
-- 或
SELECT columns
FROM table1
RIGHT OUTER JOIN table2 ON table1.column = table2.column;
案例
-- 右连接查询
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：返回所有部门，包括没有员工的部门
-- 张三 - 技术部
-- 李四 - 销售部
-- 王五 - 技术部
-- NULL - 人事部
4. 全连接 (FULL JOIN) / 全外连接 (FULL OUTER JOIN)
原理
全连接返回左表和右表中的所有记录。当某一行在另一个表中没有匹配时，另一个表的部分将包含NULL值。

语法
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
-- 或
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;
案例
-- 全连接查询
SELECT e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

-- 结果：返回所有员工和所有部门的组合
-- 张三 - 技术部
-- 李四 - 销售部
-- 王五 - 技术部
-- 赵六 - NULL
-- NULL - 人事部
5. 笛卡尔积 (CROSS JOIN) / 交叉连接
原理
笛卡尔积返回两个表中所有行的组合，即第一个表的每一行与第二个表的每一行进行组合。

语法
-- 标准语法
SELECT columns
FROM table1
CROSS JOIN table2;

-- 传统语法
SELECT columns
FROM table1, table2;
案例
-- 笛卡尔积查询
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- 结果：4个员工 × 3个部门 = 12条记录
-- 张三 - 技术部
-- 张三 - 销售部
-- 张三 - 人事部
-- 李四 - 技术部
-- ... (共12行)
6. 自然连接 (NATURAL JOIN)
原理
自然连接基于两个表中具有相同名称的列自动进行连接。

语法
SELECT columns
FROM table1
NATURAL JOIN table2;
案例
-- 假设两个表都有dept_id列
SELECT emp_name, dept_name
FROM employees
NATURAL JOIN departments;
7. 自连接 (SELF JOIN)
原理
自连接是指表与自身进行连接，常用于处理层次结构数据。

语法
SELECT a.columns, b.columns
FROM table a
JOIN table b ON a.column = b.column;
案例
-- 创建包含经理信息的员工表
CREATE TABLE employees_mgr (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    manager_id NUMBER
);

INSERT INTO employees_mgr VALUES (1, 'CEO', NULL);
INSERT INTO employees_mgr VALUES (2, '技术总监', 1);
INSERT INTO employees_mgr VALUES (3, '开发经理', 2);
INSERT INTO employees_mgr VALUES (4, '开发工程师', 3);

-- 自连接查询员工及其经理
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees_mgr e
LEFT JOIN employees_mgr m ON e.manager_id = m.emp_id;

-- 结果：
-- CEO - NULL
-- 技术总监 - CEO
-- 开发经理 - 技术总监
-- 开发工程师 - 开发经理
8. 使用USING子句的连接
原理
当连接的两个表有相同名称的连接列时，可以使用USING子句简化语法。

语法
SELECT columns
FROM table1
JOIN table2 USING (common_column);
案例
-- 使用USING子句
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d USING (dept_id);
9. 多表连接
原理
可以连接两个以上的表，构建更复杂的查询。

语法
SELECT columns
FROM table1
JOIN table2 ON condition1
JOIN table3 ON condition2
...;
案例
-- 创建第三个表：项目表
CREATE TABLE projects (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(50),
    dept_id NUMBER
);

INSERT INTO projects VALUES (1, '项目A', 10);
INSERT INTO projects VALUES (2, '项目B', 20);

-- 多表连接查询
SELECT e.emp_name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN projects p ON d.dept_id = p.dept_id;

-- 结果：显示员工、部门和项目的关联信息
10. 连接的性能优化技巧
使用索引
-- 在连接列上创建索引
CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_dept_id ON departments(dept_id);
使用WHERE子句过滤
-- 在连接后使用WHERE过滤
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部';
使用子查询优化
-- 对于大表，可以先过滤再连接
SELECT e.emp_name, d.dept_name
FROM (SELECT * FROM employees WHERE emp_id < 100) e
JOIN departments d ON e.dept_id = d.dept_id;
总结对比
连接类型        关键字        返回结果        说明
内连接        INNER JOIN        两个表都匹配的行        最常用
左连接        LEFT JOIN        左表所有行 + 右表匹配行        保留左表所有数据
右连接        RIGHT JOIN        右表所有行 + 左表匹配行        保留右表所有数据
全连接        FULL JOIN        两个表所有行        保留所有数据
交叉连接        CROSS JOIN        两个表的笛卡尔积        慎用，数据量大
自然连接        NATURAL JOIN        自动匹配同名列        简洁但不够明确
注意事项
NULL值处理：连接条件中的NULL值不会匹配
性能考虑：连接多个大表时要注意性能
索引使用：连接列应该有适当的索引
别名使用：多表连接时建议使用表别名
连接条件：确保连接条件正确，避免意外笛卡尔积