SQLite FULL OUTER JOIN

SQLite 全外连接

在 SQLite 中，FULL OUTER JOIN 子句允许您根据相关列合并两个表中的行。

FULL OUTER JOIN 子句返回第一张表和第二张表中的所有行。如果其中一个表中没有匹配的行，则使用 NULL 来表示缺失的值。

请注意，FULL OUTER JOIN 的结果是 LEFT JOIN 和 RIGHT JOIN 的结果的组合。

全外连接子句：

SELECT select_list
FROM table1
FULL OUTER JOIN table2 ON table2.column2 = table2.column1;

如果column1和column2的名称相同，你可以使用USING syntax.

SELECT select_list
FROM table1
FULL OUTER JOIN table2 USING (column_name);

如果要从 table1 中查找在 table2 中没有匹配行的行，可以使用以下语句：

SELECT
  select_list
FROM
  table1
  FULL OUTER JOIN table2 USING (column_name)
WHERE
  table2.column_name IS NULL;

类似地，您可以使用以下查询从 table2 中查找在 table1 中没有匹配行的行：

SELECT
  select_list
FROM
  table1
  FULL OUTER JOIN table2 USING (column_name)
WHERE
  table1.column_name IS NULL;

SQLite FULL OUTER JOIN 例子

首先，创建3个表 students, courses和enrollments.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    FOREIGN KEY(student_id) REFERENCES students(student_id),
    FOREIGN KEY(course_id) REFERENCES courses(course_id)
);

向表中插入行：

INSERT INTO
  students (student_name)
VALUES
  ('John'),
  ('Jane'),
  ('Doe'),
  ('Alice'),
  ('Bob');

INSERT INTO
  courses (course_name)
VALUES
  ('Math'),
  ('Science'),
  ('History');

INSERT INTO
  enrollments (student_id, course_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (4, NULL),
  (NULL, 3);

使用全外连接来找学生选课：

SELECT
  s.student_name,
  c.course_name
FROM
  students s
  FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
  FULL OUTER JOIN courses c ON e.course_id = c.course_id;
  

输出：

student_name  course_name
------------  -----------
John          Math
Jane          Science
Doe           History
Alice         null
Bob           null
null          History

输出显示 Alice 和 Bob 没有选修任何课程，并且没有学生选修历史课程。

由于表共享相同的列名，因此可以使用 USING 语法：

SELECT
  student_name,
  course_name
FROM
  students
  FULL OUTER JOIN enrollments USING(student_id)
  FULL OUTER JOIN courses USING (course_id);
  

查找没有选任何课的学生：

SELECT
  student_name,
  course_name
FROM
  students
  FULL OUTER JOIN enrollments USING (student_id)
  FULL OUTER JOIN courses USING (course_id)
WHERE
  course_name IS NULL;
  

最后，找出还没有任何学生选修的课程：

SELECT
  student_name,
  course_name
FROM
  students
  FULL OUTER JOIN enrollments USING (student_id)
  FULL OUTER JOIN courses USING (course_id)
WHERE
  student_name IS NULL;

输出：

student_name  course_name
------------  -----------
null          History