mysql进阶操作join篇(三)

本文主要介绍mysql中常用join和in,exists 的转化

mysql 版本：

• 建表

-- 建表
CREATE TABLE `product_name_copy` (
  `id` bigint NOT NULL DEFAULT '0',
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '产品名',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

• left join

-- 查询A表有而B表没有的数据
select * from demo.product_name_copy a left join demo.product_name b on a.name = b.name  where b.name is null ;
select * from demo.product_name_copy a left outer join demo.product_name b on a.name = b.name  where b.name is null ;
select * from demo.product_name_copy  a  where a.name not  in (select name from demo.product_name) ;
select * from demo.product_name_copy  a  where not EXISTS(select name from demo.product_name b where a.name = b.name) ;

• right join

-- 查询A表没有而B表有的数据
select * from demo.product_name a right join demo.product_name_copy b on a.name = b.name  where a.name is null ;

• inner join

-- 查询A和B表同时存在的数据
select * from demo.product_name_copy a  join demo.product_name b on a.name = b.name ;
select * from demo.product_name_copy a inner join demo.product_name b on a.name = b.name ;
select * from demo.product_name_copy  a  where a.name  in (select name from demo.product_name) ;
select * from demo.product_name_copy  a  where exists(select * from demo.product_name b where a.name = b.name )  ;

• full join ()

-- mysql 不存在full join 
-- full join  
-- full join 原始写法1 , 1=1 写法慎用，会出现笛卡尔积，内存爆炸
select * fr om demo.product_name_copy a  FULL JOIN   demo.product_name b on 1=1; -- 写法不存在
-- full join  替代方案1
select * from demo.product_name_copy a left join demo.product_name b on  1=1
union 
select * from demo.product_name_copy a right join demo.product_name b on 1=1
;
-- full join原始写法2
select * from demo.product_name_copy a  FULL JOIN   demo.product_name b on a.name=b.name; -- 写法不存在
-- full join替代方案2
select * from demo.product_name_copy a left join demo.product_name b on  a.name=b.name
union 
select * from demo.product_name_copy a right join demo.product_name b on a.name=b.name;