多表查询

多表查询的结果是一个笛卡尔乘积（一个集合中的每一条数据和另一个集合的每一条集合结合叫笛卡尔乘积） 详解SQL join

SQL join

学习准备

1. 创建products表

CREATE TABLE `products` (

  `id` int NOT NULL AUTO_INCREMENT,

  `name` varchar(20) DEFAULT NULL,

  `price` int DEFAULT NULL,

  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

  `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `brand_id` int DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `brand_id` (`brand_id`),

  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE

)

2. 创建brand表

CREATE TABLE `brand` (

  `id` int NOT NULL AUTO_INCREMENT,

  `name` varchar(20) DEFAULT NULL,

  `url` varchar(100) DEFAULT NULL,

  `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

)

products表通过brand_id引用brand表

多表查询SQL学习-SQL join

left join

1. 查询所有手机（包括没有品牌信息的手机）以及对应的品牌信息: SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id;

2. 查询没有对应品牌数据的产品： SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id where brand.id IS NULL;

right join

1. 查询所有的品牌以及品牌对应的产品信息： SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id;

2. 查询没有对应产品的品牌信息 SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id WHERE products.brand_id IS NULL;

inner join

1. 查询产品信息并且该产品有对应的品牌信息: SELECT * FROM products JOIN brand ON products.brand_id = brand.id;

等价于：

SELECT * FROM products, brand WHERE products.brand_id = brand.id;

full outer join

MySQL不支持FULL JOIN关键字，full out join 相当于 left join union right join

1. 查询产品及品牌信息

(SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id) UNION
    (SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id);

full outer join excluding inner join

(SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id WHERE brand.id IS NULL) UNION
    (SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id WHERE products.brand_id IS NULL);