SQL 之 JOIN

Contents

本文通过实例对 SQL 的 Join 进行简单的介绍:join 大致分为以下七种情况:

image

一、数据

SQL语句

/*
Navicat MySQL Data Transfer

Source Server         : zuolin
Source Server Version : 50714
Source Host           : localhost:3306
Source Database       : learn

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2018-06-20 16:08:42
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_a
-- ----------------------------
DROP TABLE IF EXISTS `test_a`;
CREATE TABLE `test_a` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_a
-- ----------------------------
INSERT INTO `test_a` VALUES ('1', '苹果');
INSERT INTO `test_a` VALUES ('2', '橘子');
INSERT INTO `test_a` VALUES ('3', '菠萝');
INSERT INTO `test_a` VALUES ('4', '香蕉');
INSERT INTO `test_a` VALUES ('5', '西瓜');


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_b
-- ----------------------------
DROP TABLE IF EXISTS `test_b`;
CREATE TABLE `test_b` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_b
-- ----------------------------
INSERT INTO `test_b` VALUES ('1', '梨子');
INSERT INTO `test_b` VALUES ('2', '苹果');
INSERT INTO `test_b` VALUES ('3', '草莓');
INSERT INTO `test_b` VALUES ('4', '桃子');
INSERT INTO `test_b` VALUES ('5', '香蕉');

二、示例介绍

1. Inner join

产生 A 和 B 的交集。Inner join 叫做等值连接,即需要指定等值连接条件.

SELECT * FROM test_a INNER JOIN test_b ON test_a.name = test_b.name   

image1

2. Full join (Full outer join)

产生 A 和 B 的并集。对于没有匹配的记录,则以 null 做为值。

SELECT * FROM test_a FULL JOIN test_b ON test_a.name = test_b.name 

image2

3. Left join (Left outer join )

产生表 A 的完全集,而 B 表中匹配的则有值,没匹配的以 null 值取代。

SELECT * FROM  test_a LEFT JOIN test_b ON test_a.name = test_b.name 

image3

4. Left join on where (Left outer join on where)

产生在A表中有而在 B 表中没有的集合。

SELECT * FROM test_a LEFT JOIN test_b ON test_a.name = test_b.name WHERE  test_b.name IS NULL  

image4

5. RIGHT JOIN (RIGHT OUTER JOIN)

产生表 B 的完全集,而A表中匹配的则有值,没匹配的以 null 值取代。

SELECT * FROM test_a RIGHT JOIN test_b ON test_a.name = test_b.name  

image5

6. right join on where (right outer join on where)

产生在B表中有而在 A 表中没有的集合。

SELECT * FROM test_a RIGHT OUTER JOIN test_b ON test_a.name = test_b.name WHERE test_a.name IS NULL  

image6

7. FULL JOIN WHERE (FULL OUTER JOIN WHERE)

产生(A 表中有但 B 表没有)和(B 表中有但 A 表中没有)的数据集。

SELECT * FROM test_a FULL JOIN test_b ON test_a.name = test_b.name  WHERE test_a.name IS NULL OR test_b.name IS NULL 

image7

8. Cross join

表A和表B的数据进行一个N*M的组合,即笛卡尔积(交差集)。由于其返回的结果为被连接的两个数据表的乘积,因此当有 WHERE,ON 条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢。一般使用 LEFT [OUTER] JOIN 或者 RIGHT [OUTER] JOIN

三、注意

  1. MySQL不支持全连接,可使用联合UNION来模拟,比如,第 2 个中的全连接,可以使用
    SELECT * FROM test_a LEFT JOIN test_b ON test_a.name = test_b.name 
    UNION 
    SELECT * FROM test_a RIGHT JOIN test_b ON test_a.name = test_b.name
    

    image8
    UNION ALL 返回重复值。


转载请注明:yezuolin的博客 » 点击阅读原文