SQL 连接如何工作

SQL连接的工作原理及案例代码解析

sql
-- 创建两个示例表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT
);
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);
-- 插入示例数据
INSERT INTO employees VALUES (1, 'John Doe', 101);
INSERT INTO employees VALUES (2, 'Jane Smith', 102);
INSERT INTO employees VALUES (3, 'Bob Johnson', 101);
INSERT INTO departments VALUES (101, 'HR');
INSERT INTO departments VALUES (102, 'IT');
-- 执行内连接查询
SELECT employees.employee_id, employee_name, department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

sql
-- 执行左连接查询
SELECT employees.employee_id, employee_name, department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

sql
-- 执行右连接查询
SELECT employees.employee_id, employee_name, department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

sql
-- 执行全外连接查询
SELECT employees.employee_id, employee_name, department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;