MSSQL——掌握叉号运算符的秘诀

1. 叉号运算符简介

在SQL中，叉号运算符也称为交（JOIN）运算符，其作用是将两个或多个表中的数据连接在一起，以便进行更高级别的数据分析。

在进行JOIN操作时，需要指定一个条件来确定连接的结果，这个条件就是JOIN条件。常用JOIN操作包括INNER JOIN、OUTER JOIN、LEFT JOIN、RIGHT JOIN等。

下面我们就来详细介绍一下这些JOIN操作。

2. INNER JOIN

INNER JOIN是最常用的JOIN操作之一，它返回两个表中符合连接条件的记录。

例如，我们有两个表students和scores，它们的结构如下：

-- students表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);
-- 插入数据
INSERT INTO students VALUES (1, '张三', 18);
INSERT INTO students VALUES (2, '李四', 20);
INSERT INTO students VALUES (3, '王五', 19);
INSERT INTO students VALUES (4, '赵六', 18);
-- scores表
CREATE TABLE scores (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score INT NOT NULL
);
-- 插入数据
INSERT INTO scores VALUES (1, 1, '语文', 90);
INSERT INTO scores VALUES (2, 1, '数学', 95);
INSERT INTO scores VALUES (3, 2, '语文', 88);
INSERT INTO scores VALUES (4, 2, '数学', 93);
INSERT INTO scores VALUES (5, 3, '语文', 85);
INSERT INTO scores VALUES (6, 3, '数学', 90);
INSERT INTO scores VALUES (7, 4, '语文', 92);
INSERT INTO scores VALUES (8, 4, '数学', 89);

现在我们要查询张三的所有成绩，可以使用下面的SQL语句：

SELECT students.name, scores.subject, scores.score
FROM students
INNER JOIN scores
ON students.id = scores.student_id
WHERE students.name = '张三';

上述SQL语句中，我们使用INNER JOIN将students表和scores表连接在一起，连接条件是students.id等于scores.student_id，并且限制students表中name字段的值等于'张三'。运行上述SQL语句得到如下结果：

 name | subject | score
------+---------+-------
 张三 | 语文    |    90
 张三 | 数学    |    95

结果表明，张三的语文成绩是90分，数学成绩是95分。

3. OUTER JOIN

OUTER JOIN也是常用的JOIN操作之一，它返回左表和右表中所有符合连接条件的记录，如果有一方没有符合条件的记录，则使用null来填充。

常用的OUTER JOIN包括LEFT OUTER JOIN和RIGHT OUTER JOIN。

例如，我们有两个表students和classes，它们的结构如下：

-- students表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    class_id INT NOT NULL
);
-- 插入数据
INSERT INTO students VALUES (1, '张三', 18, 1);
INSERT INTO students VALUES (2, '李四', 20, 2);
INSERT INTO students VALUES (3, '王五', 19, 1);
INSERT INTO students VALUES (4, '赵六', 18, 3);
-- classes表
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
-- 插入数据
INSERT INTO classes VALUES (1, '一班');
INSERT INTO classes VALUES (2, '二班');
INSERT INTO classes VALUES (3, '三班');

现在我们要查询所有学生的姓名和所在班级名称，如果某个学生没有对应的班级，则姓名和班级名称都应该显示为null。可以使用下面的SQL语句：

SELECT students.name, classes.name
FROM students
LEFT OUTER JOIN classes
ON students.class_id = classes.id;

上述SQL语句中，我们使用LEFT OUTER JOIN将students表和classes表连接在一起，连接条件是students.class_id等于classes.id。运行上述SQL语句得到如下结果：

 name | name
------+-------
 张三 | 一班
 李四 | 二班
 王五 | 一班
 赵六 | 三班

结果表明，张三、李四和王五有对应的班级，赵六没有对应的班级。

4. LEFT JOIN

LEFT JOIN也是连接两个表的操作，它将返回左表中所有符合条件的记录和右表中符合条件的记录，如果右表中没有符合条件的记录，则使用null来填充。

例如，我们有两个表students和scores，它们的结构如下：

-- students表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);
-- scores表
CREATE TABLE scores (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score INT NOT NULL
);
-- 插入数据
INSERT INTO students VALUES (1, '张三', 18);
INSERT INTO students VALUES (2, '李四', 20);
INSERT INTO students VALUES (3, '王五', 19);
INSERT INTO students VALUES (4, '赵六', 18);
INSERT INTO scores VALUES (1, 1, '语文', 90);
INSERT INTO scores VALUES (2, 1, '数学', 95);
INSERT INTO scores VALUES (5, 3, '语文', 85);
INSERT INTO scores VALUES (6, 3, '数学', 90);

现在我们要查询每个学生的成绩，如果没有成绩，则该学生的成绩应该为null。可以使用下面的SQL语句：

SELECT students.name, scores.subject, scores.score
FROM students
LEFT JOIN scores
ON students.id = scores.student_id;

上述SQL语句中，我们使用LEFT JOIN将students表和scores表连接在一起，连接条件是students.id等于scores.student_id。运行上述SQL语句得到如下结果：

 name | subject | score
------+---------+-------
 张三 | 语文    |    90
 张三 | 数学    |    95
 李四 | null    |  null
 王五 | 语文    |    85
 王五 | 数学    |    90
 赵六 | null    |  null

结果表明，张三和王五有对应的成绩，李四和赵六没有对应的成绩。

5. RIGHT JOIN

RIGHT JOIN和LEFT JOIN类似，只是它返回右表中所有符合条件的记录和左表中符合条件的记录，如果左表中没有符合条件的记录，则使用null来填充。

例如，我们有两个表departments和employees，它们的结构如下：

-- departments表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
-- employees表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL
);
-- 插入数据
INSERT INTO departments VALUES (1, '财务部');
INSERT INTO departments VALUES (2, '市场部');
INSERT INTO departments VALUES (3, '人力资源部');
INSERT INTO employees VALUES (1, '张三', 1);
INSERT INTO employees VALUES (2, '李四', 2);
INSERT INTO employees VALUES (3, '王五', 2);
INSERT INTO employees VALUES (4, '赵六', 3);

现在我们要查询每个部门中的所有员工，如果没有员工，则该部门的员工应该为null。可以使用下面的SQL语句：

SELECT departments.name, employees.name
FROM departments
RIGHT JOIN employees
ON departments.id = employees.department_id;

上述SQL语句中，我们使用RIGHT JOIN将departments表和employees表连接在一起，连接条件是departments.id等于employees.department_id。运行上述SQL语句得到如下结果：

  name    | name
----------+------
 财务部   | null
 市场部   | 李四
 市场部   | 王五
 人力资源部 | 赵六

结果表明，市场部有李四和王五两个员工，其他部门没有员工。

6. 总结

通过本文的介绍，我们对叉号运算符在SQL中的使用有了更加深入的理解。不同类型的JOIN操作具有不同的功能和作用，我们需要根据自己的需求来选择合适的JOIN操作。