文章目录

  • 1. 数据库表连接方式简介
    • 2. 内连接(INNER JOIN)
    • 3. 左外连接(LEFT JOIN)
    • 4. 右外连接(RIGHT JOIN)
    • 5. 全连接(FULL OUTER JOIN)
    • 6. 交叉连接(CROSS JOIN)
    • 7. 联合查询(UNION)
  • 8. 案例分析
    • 9. 代码示例
  • 各个连接的区别

1. 数据库表连接方式简介

在关系型数据库中,表与表之间的连接是非常重要的操作。连接可以将两个或多个表中的数据进行合并,实现数据的查询和分析。常见的连接方式有内连接、左连接、右连接、全连接、交叉连接和联合查询。下面将分别对这几种连接方式进行详细介绍。

2. 内连接(INNER JOIN)

用比较运算符比较要连接的列的值的连接,不匹配的行不会被显示。sql关键字JOIN 或者INNER JOIN,通常写成JOIN。
内连接只返回两个表中共有的行,即两个表中连接字段的值相等的行。内连接的语法为:

SELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name = table2.column_name;

3. 左外连接(LEFT JOIN)

① 外连接又分为:左外连接、右外连接、全外连接。

② 对应的sql关键字:LEFT/RIGHT/FULL OUTER JOIN,通常省略OUTER关键字,写成LEFT/RIGHT/FULL JOIN。

③ 在左、右外连接中都会以一种表为基表,基表的所有行、列都会显示,外表如果和条件不匹配则所有的外表列值都为NULL。

全外连接则所有表的行、列都会显示,条件不匹配的值皆为NULL。

左连接返回左表中的所有行,以及右表中连接字段的值和左表相等的行。如果右表中没有符合条件的行,则返回 NULL 值。左连接的语法为:

SELECT column_name(s)FROM table1LEFT JOIN table2ON table1.column_name = table2.column_name;

4. 右外连接(RIGHT JOIN)

右连接和左连接相反,返回右表中的所有行,以及左表中连接字段的值和右表相等的行。如果左表中没有符合条件的行,则返回 NULL 值。右连接的语法为:

SELECT column_name(s)FROM table1RIGHT JOIN table2ON table1.column_name = table2.column_name;

5. 全连接(FULL OUTER JOIN)

没有where条件的交叉连接将产生连接表所涉及的笛卡尔积,即TableA的行数*TableB的行数的结果集。如果带where,返回或显示的是匹配的行数。(cross join后只能用where不能是on)

全连接返回左右两个表中的所有行,并将左表和右表中连接字段的值相等的行合并成一行。如果左表或右表中没有符合条件的行,则返回 NULL 值。全连接的语法为:

SELECT column_name(s)FROM table1FULL OUTER JOIN table2ON table1.column_name = table2.column_name;

6. 交叉连接(CROSS JOIN)

交叉连接返回两个表中的所有可能的组合,也称为笛卡尔积。交叉连接的语法为:

SELECT column_name(s)FROM table1CROSS JOIN table2;

7. 联合查询(UNION)

联合查询将两个或多个表中的数据合并成一个结果集,不包括重复的行。要求每个表中的列数和数据类型必须完全一致。联合查询的语法为:

SELECT column_name(s)FROM table1UNIONSELECT column_name(s)FROM table2;

8. 案例分析

假设有两个表,分别为表 A 和表 B,它们的数据分别如下:

表 A:

idname
1Alice
2Bob
3Cathy
4David
5Edward

表 B:

idage
225
330
435
640

现在需要通过连接查询得到一个包含两个表中所有行的结果集,它们的连接字段为 id。则不同的连接方式返回的结果如下:

  • 内连接:
idnameage
2Bob25
3Cathy30
4David35
  • 左连接:
idnameage
1AliceNULL
2Bob25
3Cathy30
4David35
5EdwardNULL
  • 右连接:
idnameage
2Bob25
3Cathy30
4David35
6NULL40
  • 全连接:
idnameage
1AliceNULL
2Bob25
3Cathy30
4David35
5EdwardNULL
6NULL40
  • 交叉连接:
idnameage
1Alice25
1Alice30
1Alice35
1Alice40
2Bob25
2Bob30
2Bob35
2Bob40
3Cathy25
3Cathy30
3Cathy35
3Cathy40
4David25
4David30
4David35
4David40
5Edward25
5Edward30
5Edward35
5Edward40
  • 联合查询:
idname
1Alice
2Bob
3Cathy
4David
5Edward
2NULL
3NULL
4NULL
6NULL

9. 代码示例

下面是针对上述案例的 SQL 代码示例:

-- 创建表 A 并插入数据CREATE TABLE A (id INT PRIMARY KEY,name VARCHAR(50));INSERT INTO A VALUES (1, 'Alice');INSERT INTO A VALUES (2, 'Bob');INSERT INTO A VALUES (3, 'Cathy');INSERT INTO A VALUES (4, 'David');INSERT INTO A VALUES (5, 'Edward');-- 创建表 B 并插入数据CREATE TABLE B (id INT PRIMARY KEY,age INT);INSERT INTO B VALUES (2, 25);INSERT INTO B VALUES (3, 30);INSERT INTO B VALUES (4, 35);INSERT INTO B VALUES (6, 40);-- 内连接查询SELECT A.id, A.name, B.ageFROM AINNER JOIN BON A.id = B.id;-- 左连接查询SELECT A.id, A.name, B.ageFROM ALEFT JOIN BON A.id = B.id;-- 右连接查询SELECT A.id, A.name, B.ageFROM ARIGHT JOIN BON A.id = B.id;-- 全连接查询SELECT A.id, A.name, B.ageFROM AFULL OUTER JOIN BON A.id = B.id;-- 交叉连接查询SELECT A.id, A.name, B.ageFROM ACROSS JOIN B;-- 联合查询SELECT id, nameFROM AUNIONSELECT id, NULL AS nameFROM B;

各个连接的区别

连接方式含义结果左表右表参与条件是否保留未匹配数据
内连接(INNER JOIN)只返回两个表中存在匹配的行匹配的行表A表BON条件
左外连接(LEFT JOIN)返回左表中所有行和右表中与左表匹配的行匹配的行+左表未匹配行null值表A表BON条件
右外连接(RIGHT JOIN)返回右表中所有行和左表中与右表匹配的行匹配的行+右表未匹配行null值表A表BON条件
全连接(FULL OUTER JOIN)返回左表和右表中所有的行,如果匹配,则返回匹配的行匹配的行+左表未匹配行+右表未匹配行null值表A表BON条件
交叉连接(CROSS JOIN)返回两个表中所有匹配的行笛卡尔积表A表B无(不需要条件)
联合查询(UNION)把两个表中的结果集合并在一起并去重合并且去重后的行表A表B无(两个表结构和列数必须一致)