full join以及where条件查询

简介：

　　1、left join：左连接查询 - 以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左表无效。

　　2、rigth join：右连接查询 - 以右表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对右表无效。

　　3、inner join：内连接查询 - 等值连接，根据过滤条件生成临时表。用inner join 后面的条件可以用 where实现。

　　4、full join：全连接查询 - 以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左右表无效（MySQL不支持FULL JOIN可以使用UNION ALL将查询组合）

　　5、where：对生成的临时表进行过滤，inner join能完成的功能用where条件都可以完成，但反之则不是。

效率：

　　left join，right join效率要高于inner join和where条件查询，因inner join和where需要生成临时表，然后按照条件过滤临时表结果得出想要的数据。

语法：

--建表语句
CREATE TABLE `t_salecategory_product_relation` (
  `relation_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键列',
  `product_id` int(11) NOT NULL COMMENT '商品ID，外键',
  `product_code` varchar(32) NOT NULL COMMENT '商品编码',
  `category_id` bigint(20) NOT NULL COMMENT '运营分类ID，外键，对应表t_sale_category中的主键列',
  `category_code` varchar(64) NOT NULL COMMENT '运营分类编号',
  `order_value` int(11) DEFAULT NULL COMMENT '排序值，在搜索时使用，按降序排',
  `mount_type` smallint(6) NOT NULL COMMENT '挂载类型：\r\n                1:自动挂载；\r\n                2：手动挂载\r\n            ',
  `opt_type` smallint(6) DEFAULT NULL COMMENT '操作类型: 1 更新  2 删除 ,默认为1',
  `mount_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '挂载时间',
  `mount_user` varchar(64) DEFAULT NULL COMMENT '挂载人',
  `last_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后修改时间',
  `last_update_user` varchar(64) DEFAULT NULL COMMENT '最后修改人',
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `IDX_productcode_salecode` (`product_code`,`category_code`),
  KEY `FK_product_saleCategory` (`category_id`),
  KEY `FK_salCatProduct_prdInfo` (`product_id`),
  CONSTRAINT `FK_salCatProduct_prdInfo` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE,
  CONSTRAINT `FK_FK_saleCategory_saleCategoryProductRelation` FOREIGN KEY (`category_id`) REFERENCES `t_sale_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 COMMENT='运营分类和商品挂载关系表';

CREATE TABLE `t_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品ID',
  `product_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `product_code` varchar(32) DEFAULT NULL COMMENT '商品编码',
  `product_desc` varchar(512) DEFAULT NULL COMMENT '商品描述',
  `product_shelves` int(1) DEFAULT NULL COMMENT '商品上下架状态',
  `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
  `create_by` int(11) DEFAULT NULL COMMENT '创建人',
  `create_user_name` varchar(255) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL COMMENT '最后修改时间',
  `update_by` int(11) DEFAULT NULL COMMENT '最后修改人',
  `update_user_name` varchar(255) DEFAULT NULL,
  `first_shelves` int(11) DEFAULT NULL COMMENT '第一次上架人ID',
  `first_shelves_name` varchar(32) DEFAULT NULL COMMENT '第一次上架 人名称',
  `first_shelves_time` int(11) DEFAULT NULL COMMENT '第一次上架时间',
  `last_shelves` int(11) DEFAULT NULL COMMENT '最后一次上架人ID',
  `last_shelves_name` varchar(32) DEFAULT NULL COMMENT '最后一次上架人名称',
  `last_shelves_time` int(11) DEFAULT NULL COMMENT '最后一次上架时间',
  `down_shelves` int(11) DEFAULT NULL COMMENT '最后一次下架人ID',
  `down_shelves_name` varchar(32) DEFAULT NULL COMMENT '最后一次下架人名称',
  `down_shelves_time` int(11) DEFAULT NULL COMMENT '最后一次下架时间',
  `cost_price` double DEFAULT NULL COMMENT '成本价',
  `tsh_price` double DEFAULT NULL COMMENT '销售价',
  `tb` int(11) DEFAULT NULL COMMENT '特币',
  `market_price` double DEFAULT NULL COMMENT '市场价',
  `brand_code` varchar(16) DEFAULT NULL COMMENT '基础品牌编码',
  `brand_name` varchar(64) DEFAULT NULL COMMENT '基础品牌名称',
  `cat_code` varchar(16) DEFAULT NULL COMMENT '基础分类编码',
  `cat_name` varchar(64) DEFAULT NULL COMMENT '基础分类名称',
  `type` int(11) DEFAULT NULL COMMENT '类型',
  `staus` int(1) DEFAULT NULL COMMENT '状态',
  `main_pic` varchar(255) DEFAULT NULL COMMENT '主图',
  `supplier_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=142786916 DEFAULT CHARSET=utf8 COMMENT='商品基本属性表';

采用 inner join 过滤左表

SELECT 
    t1.relation_id,
    t1.product_id,
    t1.product_code,
    t2.product_id AS p_product_id,
    t2.product_name AS p_product_name,
    t2.product_code AS p_product_code,
FROM
    t_salecategory_product_relation t1
        JOIN
    t_product t2 ON t1.product_id = t2.product_id  and t1.category_id = 1

使用where 语句过滤，理论上效率应该比 inner join 低（测试1W+数据-表未作优化只新建主键索引100次，where平均多0.02s左右）

SELECT 
    t1.relation_id,
    t1.product_id,
    t1.product_code,
    t2.product_id AS p_product_id,
    t2.product_name AS p_product_name,
    t2.product_code AS p_product_code,
FROM
    t_salecategory_product_relation t1
        LEFT JOIN
    t_product t2 ON t1.product_id = t2.product_id
WHERE
    t1.category_id = 1;

错误的语句，左连接left join 时对左表的过滤失效，即 t1.category_id = 1 条件不起效

SELECT 
    t1.relation_id,
    t1.product_id,
    t1.product_code,
    t2.product_id AS p_product_id,
    t2.product_name AS p_product_name,
    t2.product_code AS p_product_code,
FROM
    t_salecategory_product_relation t1
        LEFT JOIN
    t_product t2 ON t1.product_id = t2.product_id  and t1.category_id = 1

以上语句修改：

SELECT 
    t1.relation_id,
    t1.product_id,
    t1.product_code,
    t2.product_id AS p_product_id,
    t2.product_name AS p_product_name,
    t2.product_code AS p_product_code,
FROM
    t_salecategory_product_relation t1
        LEFT JOIN
    t_product t2 ON t1.product_id = t2.product_id 
where t1.category_id = 1

　　上边这条SQL语句是对left join结果做虚拟表，然后通过where条件过滤，比直接使用where效率要高些。right join同left join。

以上内容是通过查阅资料和实际使用归纳总结的来，若有什么不对的地方欢迎留言。

参考：

https://www.cnblogs.com/xunux/p/4432770.html

https://www.cnblogs.com/lijingran/p/9001302.html

https://blog.csdn.net/minixuezhen/article/details/79763263

posted @ 2019-04-03 15:01 杏仁拌饭阅读(1156) 评论(0) 收藏举报

刷新页面返回顶部