ASPIRE

MySQL多表join连接查询

=Start=

缘由：

因为在大学的时候SQL学的不扎实，后来工作中也没有专门深入了解过，虽然一些常规/常见功能的SQL写的也很流畅，但是在碰到复杂场景（需要多个表各种join）的时候就不行了，有点发虚，这次趁着一个机会好好学习理解一下MySQL多表join连接查询的知识，也方便以后参考。

正文：

参考解答：

常见的 join 按照功能大致分为如下三类：

  • INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

先用2张图直观的表示一下各种join的作用：

&

然后再通过一些本地环境造数据进行测试以加深影响：

/*
创建测试表
*/
CREATE DATABASE `do_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE do_test;

CREATE TABLE `table1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sex` varchar(100) NOT NULL,
  `age` int(11) unsigned,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `table2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sex` varchar(100) NOT NULL,
  `age` int(11) unsigned,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4


/*
填充测试数据
*/
INSERT INTO `table1`
	(name, age, sex)
VALUES
	('Pirate', 22, 'male'),
	('Monkey', 19, 'male'),
	('Ninja', 26, 'female'),
	('Spaghetti', 25, 'male')
;

INSERT INTO `table2`
	(name, age, sex)
VALUES
	('Rutabaga', 22, 'female'),
	('Pirate', 22, 'male'),
	('Darth Vader', 25, 'male'),
	('Ninja', 26, 'female')
;


/*
进行测试
*/
-- 以下两句SQL的功能等价
select A.id,A.name,B.name from table1 A, table2 B where A.id = B.id;
select A.id,A.name,B.name from table1 A inner join table2 B on A.id = B.id;

-- 以下两句SQL的功能等价
select A.id,A.name,B.name from table1 A, table2 B where A.name = B.name;
select A.id,A.name,B.name from table1 A inner join table2 B on A.name = B.name;

select * from table1 A left join table2 B on A.name = B.name;

select * from table1 A left join table2 B on A.name = B.name where A.id is null or B.id is null;

select * from table1 A right join table2 B on A.name = B.name;

 

参考链接：

=END=

Docker

, ,
,

《 “MySQL多表join连接查询” 》 有 5 条评论

  1. [一起学Hive]之十一-Hive中Join的类型和用法
    http://lxw1234.com/archives/2015/06/315.htm
    `
    Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFT SEMI JOIN和CROSS JOIN，但这两种JOIN类型也可以用前面的代替。

    注意：Hive中Join的关联键必须在ON()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。

    以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。
    是否指定OUTER关键字，对查询结果无影响。
    `

    回复

  2. Hive: LEFT JOIN vs JOIN gives different results with filter in ON clause
    https://stackoverflow.com/questions/39271323/hive-left-join-vs-join-gives-different-results-with-filter-in-on-clause
    https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior
    `
    左表就是保留行的表(“preserved row table”)，右表是NULL供应表(“null supplying table”)。
    `

    Hive的left join、left outer join和left semi join三者的区别
    http://www.crazyant.net/1470.html

    回复

  3. LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
    https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server
    `
    Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent. (在SQL Server中2者是等价的，其它的里面貌似也是)
    left join是left outer join的简写。
    `
    left join 和 left outer join 的区别
    https://www.cnblogs.com/cy163/archive/2008/10/16/1312920.html

    SQL LEFT JOIN
    https://www.dofactory.com/sql/left-outer-join
    https://www.w3schools.com/sql/sql_join_left.asp
    `
    LEFT JOIN and LEFT OUTER JOIN are the same.
    Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
    `
    https://dba.stackexchange.com/questions/73087/mysql-which-join-is-better-between-left-outer-join-and-inner-join

    回复

  4. 轻松玩转hive中各种join之间的关系以及使用
    https://blog.csdn.net/qq_26442553/article/details/79465608
    `
    hive中的join只支持等值join,也就是说join on中的on里面表之间连接条件只能是=，不能是等符号。此外，on中的等值连接之间只能是and,不能是or. （如果在on 里添加非表之间的条件可以是非等号）

    1.inner join(内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来)
    内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法

    2.left join 是左外连接(Left Outer Jion)，其中outer可以省略，left outer join是早期的写法。
    A left join B 是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
    意思说,左表(A)的记录将会全部表示出来(不管右边的表中是否存在与它们匹配的行),而右表(B)只会显示符合搜索条件的记录，比如符合on,where中的条件。B表记录不足的地方均为NULL. A left join B 等价B right join A

    3.right join 同理和left join相反 A right join B 显示B表中所有的记录，A表不足的用null填充
    同样 right outer join = right join ,outer可以省略。

    4. full outer join 等价 full join 全外连接
    包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

    5.自连接是自身连接，指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据

    6.外连接与条件配合使用
    当在内连接（join或者inner join）查询中加入条件时，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的
    但对于外连接情况就不同了。加入的条件在join子句中和在where子句中效果完全不一样。
    当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。
    如果将条件放到where子句中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。
    `

    回复

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注