###表结构
看两个表tb_order和tb_user_info的结构,tb_order表的user_id和tb_user_info表的id存在外键关系:
CREATE TABLE `tb_order` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`product_id` BIGINT NOT NULL DEFAULT 0 COMMENT '产品id',
`product_name` varchar(64) NOT NULL DEFAULT '' COMMENT '产品名',
`user_id` int NOT NULL DEFAULT '0' COMMENT '用户id',
`spend` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '花费',
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: 否, 1: 已经删除',
INDEX `ix_user_id`(`user_id`),
INDEX `ix_product_id`(product_id),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_user_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`username` char(20) NOT NULL DEFAULT '',
`sex` TINYINT NOT NULL DEFAULT 0 COMMENT '0: 中性, 1: 男, 2: 女',
`age` INT NOT NULL DEFAULT 0,
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除' COMMENT '0: 否, 1: 已经删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tb_user_info(username, sex, age) VALUES ('Jack', 1, 24);
INSERT INTO tb_user_info(username, sex, age) VALUES ('Tom', 1, 25);
INSERT INTO tb_user_info(username, sex, age, is_delete) VALUES ('Rose', 2, 22, 1);
INSERT INTO tb_user_info(username, sex, age, is_delete) VALUES ('Hanson', 0, 26, 1);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(11, '潘多拉小饼', 1, 20);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(12, '无问西东海报', 1, 25);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(12, '星际穿越模型', 2, 25);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 3, 25);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 11, 25);
INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 12, 25);
###sql语句
1、join(inner join)查询
SELECT
o.product_id,
o.product_name,
o.user_id AS order_user_id,
u.id AS user_id,
u.username,
u.is_delete AS user_is_delete
FROM tb_order o
JOIN tb_user_info u ON o.user_id = u.id;
结果如图:
将上面的结果过滤掉user_is_delete为0的数据。
注意is_delete
分别在on条件下和where条件下:
SELECT
o.product_id,
o.product_name,
o.user_id AS order_user_id,
u.id AS user_id,
u.username,
u.is_delete AS user_is_delete
FROM tb_order o
JOIN tb_user_info u ON o.user_id = u.id AND u.is_delete = 0;
SELECT
o.product_id,
o.product_name,
o.user_id AS order_user_id,
u.id AS user_id,
u.username,
u.is_delete AS user_is_delete
FROM tb_order o
JOIN tb_user_info u ON o.user_id = u.id
WHERE u.is_delete = 0;
上面两条sql语句执行的结果一样的:
2、left join 查询
使用left join
有一个奇妙的效果。
/* u.is_delete = 0 加在了on条件下 */
/*sql1*/
SELECT
o.product_id,
o.product_name,
o.user_id AS order_user_id,
u.id AS user_id,
u.username,
u.is_delete AS user_is_delete
FROM tb_order o LEFT JOIN tb_user_info u ON o.user_id = u.id AND u.is_delete = 0
看结果:
left join
会将左边表的数据全给查询出来(即使和右边表没有mapping关系),也正好就是有一个这样的需求:需要查询出全部的订单信息,有关联用户信息的就将用户信息查出来。
现在将u.is_delete = 0
写在where 条件里:
/*sql2*/
SELECT
o.product_id,
o.product_name,
o.user_id AS order_user_id,
u.id AS user_id,
u.username,
u.is_delete AS user_is_delete
FROM tb_order o LEFT JOIN tb_user_info u ON o.user_id = u.id
WHERE u.is_delete = 0
ON o.user_id = u.id AND u.is_delete = 0
:join关联表的条件,WHERE u.is_delete = 0
:where条件里会对join之后的数据(sql1查询的结果)进行过滤筛选。
如果在后台的一个页面需要展示tb_order表的数据,显示订单的时候同时也要显示用户信息,已经被删除的用户数据不予显示。如果考虑使用left join
去连接tb_user_info 这个表的数据,就得注意tb_user_info表的is_delete字段应该放在什么位置(on后还是where后)。如果不小心放在where后了,就会少显示tb_order表的数据(这是当时项目中经历过的坑)。
这种情况下使用join并不是明智的选择,因为tb_order订单表的数据会很多,users表的数据相对会少很多,这样的两个表去做join操作,肯定影响性能。
可以先查询出订单数据,然后通过订单数据里的user_id list 使用in的方式去users表查询用户数据:
select * from tb_user_info where id in (1, 3, 4)
或直接将用户数据(比如user_id、username)给冗余到tb_order表中去,这也是大多数公司的做法。
参考链接:
MySQL LEFT JOIN
http://www.w3resource.com/sql/joins/perform-an-equi-join.php
https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/