mysql关于多个full join的处理方式

最新推荐文章于 2023-07-17 17:03:55 发布
最新推荐文章于 2023-07-17 17:03:55 发布
阅读量2.6k 收藏 1
点赞数
文章标签： java mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m690192206/article/details/107819953
版权

因为mysql当前暂不支持full join的关联方式，如果只有两张表关联的情况，可以使用left join、right join、union的方式处理，但是如果存在多张表都需要full join的方式，又如何处理呢？

1、可以采用嵌套子查询的方式，将前面的查询sql语句当成当前full查询的主表，这样相对好理解，可以理解为永远只有两张表的full join。

2、（此方式为本文重点说明的方式）那就是有几个full join，就用几个union，具体如下：

一、先建五张表：

CREATE TABLE `lsg_category` (
  `id` bigint(10) NOT NULL COMMENT '主键ID',
  `type` int(3) DEFAULT NULL COMMENT '物品类型',
  `type_name` varchar(255) NOT NULL COMMENT '物品名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物品类目表';

INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (1, 1, '水果类目');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (2, 2, '体育类目');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (3, 3, '学习用品');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (4, 4, '办公用品');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (5, 5, '游戏类目');

 

CREATE TABLE `lsg_fruit` (
  `id` bigint(10) NOT NULL COMMENT '主键ID',
  `fruit_name` varchar(255) DEFAULT NULL COMMENT '水果名称',
  `category_id` bigint(10) DEFAULT NULL COMMENT '物品类目ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='水果表';

INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (1, '苹果', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (2, '桃子', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (3, '西瓜', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (4, '橘子', 6);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (5, '西红柿', 6);

CREATE TABLE `lsg_physical` (
  `id` bigint(10) NOT NULL COMMENT '主键ID',
  `physical_name` varchar(255) DEFAULT NULL COMMENT '体育物品名称',
  `category_id` bigint(10) DEFAULT NULL COMMENT '物品类目ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='体育表';

INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (1, '篮球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (2, '足球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (3, '排球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (5, '乒乓球', 7);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (6, '橄榄球', 8);

CREATE TABLE `lsg_game` (
  `id` bigint(10) NOT NULL COMMENT '主键ID',
  `game_name` varchar(255) DEFAULT NULL COMMENT '游戏名称',
  `category_id` bigint(20) DEFAULT NULL COMMENT '类目ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='游戏表';

INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (1, '跳绳', 5);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (2, '踢毽子', 5);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (3, '捉迷藏', 15);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (4, '猜猜我是谁', 16);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (5, '划拳', 17);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (6, '剪刀石布', 17);

CREATE TABLE `lsg_learn` (
  `id` bigint(10) NOT NULL COMMENT '主键ID',
  `learn_name` varchar(255) DEFAULT NULL COMMENT '学习物品名称',
  `category_id` bigint(10) DEFAULT NULL COMMENT '类目ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学习用品表';

INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (1, '书桌', 3);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (2, '凳子', 3);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (3, '讲台', 10);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (4, '黑板', 11);

二、两张表的full join查询

（a full join b on a.id=b.id）

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`

三、3张表的full join查询

(a full join b on a.id=b.id full join c on a.id=c.id)

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`

(a full join b on a.id=b.id full join c on b.id=c.id)

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`

四、4张表的full join关联

(a full join b on a.id=b.id full join c on a.id=c.id full join d on a.id=d.id)

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
	RIGHT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id`

(a full join b on a.id=b.id full join c on b.id=c.id full join d on c.id=d.id)

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
	LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
	`lsg_game`.`id` AS `lsg_game$id`,
	`lsg_game`.`game_name` AS `lsg_game$game_name`,
	`lsg_game`.`category_id` AS `lsg_game$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
	RIGHT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id`

五张表的就不列了，以此类推，说明：每次增加一个full join，则新加一个union，而union的内容为他所关联的主表对应的那个union下的内容（直接拷贝下来，然后在末尾加上right join）。

3、如果既有full join，又有left join或者right join，如何处理呢？这个方式简单，每次遇到left join或者right join，只需要往每个union的最后加上left join或者right join即可。

我举个例子：（a full join b on a.id=b.id left join c on b.id=c.id）

SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
	`lsg_category`.`id` AS `lsg_category$id`,
	`lsg_category`.`type` AS `lsg_category$type`,
	`lsg_category`.`type_name` AS `lsg_category$type_name`,
	`lsg_fruit`.`id` AS `lsg_fruit$id`,
	`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
	`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
	`lsg_physical`.`id` AS `lsg_physical$id`,
	`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
	`lsg_physical`.`category_id` AS `lsg_physical$category_id` 
FROM
	`lsg_category` AS `lsg_category`
	RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
	LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`

批注：以上如果有不正确的地方，望指正，不胜感激。

m690192206
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
浅谈sql 三个表全连接
深海蓝的技术随笔
07-23 7145
前段时间为学校的课酬系统做升级的时候遇到这样一个问题：  教师总工作量是根据上课工作量，毕业设计指导工作量，实习指导工作量的总和来计算的。 但是问题来了，有的老师带过课，但是没有带毕业设计和实习，有的带毕业设计和生产实习的老师是外面聘请的兼职老师，没有带过课。 之前做这个系
mysql 将多个查询结果合并成一行
ChinaGir的博客
12-31 3351
1 解决方案 利用函数：group_concat()来实现 完整的语法如下： group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 2 参考 基本查询 mysql> select * from aa; +------+------+ | id| name | +------+----...
Full Join多个表与Union All多个表
weixin_43597208的博客
01-13 2247
之所以会出现这样的问题，是因为是以a表为主表，但a表中只有 id 为 1、2、3 的数据，但在b表中有id为4，c表中也有id为4，此时full join时，a表会以空值和b表、c表中id为4的数据join，这样关联后的表中就会出现2条id为4的数据；在Hive中（其他类似SQL，比如PostgreSQL可能也存在此问题），当对多张表（3张及以上）进行full join时，会存在每张表的主键都是唯一，但当full join后，会发现主键可能有重复。2.2. 查询SQL以及问题。
Hive Full Join多个表与Union All多个表
热门推荐
BITDDD小栈
12-01 1万+
一.引言 假设有如下三张表，里面分别记录了三类不用用户的购买记录，现在想将下属记录合并为 => | 用户 | 果蔬购买量 | 饮品购买量 | 零食购买量 | ，没有购买则为 Null: Table A：用户在超市近一个月购买果蔬的记录 Table B：用户在超市近一个月购买饮品的记录 Table C：用户在超市近一个月购买零食的记录 先建一个表供我们插入数据 hive -e " create table if not exists user_act_of_buying ( use
hive full join多表多关联键联合查询
weixin_41229271的博客
12-20 1996
hive full join多表多关联键联合查询
hive多表联查full join连接条件问题
qq_40772429的博客
09-13 1万+
我们在hive中会经常使用多表联查，也就是我们常做的join 或者 union 。但是在写完SQL后会发现往往实现不了业务需求，而我们却又找不到原因所在，下面是多表联查的一些经典的问题。 1. 丢数据少数据 如果我们要查询的数据每一个表有一个字段进行连接，比如连接条件为ont1.id=t2.id，若要查询符合条件的所有数据势必使用full join，如果所有id对应每个表中都有数据，那么是不会出现问题的，但是通常不会所有id在任意表中都非空，这会出现一个问题。 就拿t1,t2,t3，3个表举例，每个.
SQL使用`full outer join` 并列关联多个表
胡椒
12-29 3201
`full outer join` 一般在 SQL 中用于关联两个表取两个表的并集，百度搜索出来的都是用三个表嵌套取并集，尝试如果关联三个表平行关联，得出： 如果并列全关联，那么关键表与其他表如果关联主键相同的就会合并到同一行数据，如果不是关键表之间的关联，那么尽管主键相同，也会分散到不同行
Mysql中的join操作
12-16
3. **全外联结（FULL OUTER JOIN）**：MySQL不直接支持全外联结，但可以通过组合LEFT JOIN和RIGHT JOIN来实现相同的效果，展示左表和右表中所有匹配和不匹配的记录。 ```sql SELECT * FROM java LEFT JOIN mysql...
浅析Mysql Join语法以及性能优化
09-10
MySQL中的JOIN操作是数据库查询中不可或缺的部分，它用于在多个表之间建立关联，获取符合特定条件的数据。在本文中，我们将深入理解JOIN语法，并探讨如何进行性能优化。 首先，JOIN的基本语法涉及FROM子句后的两个...
MySQL中join语句的基本使用教程及其字段对性能的影响
12-15
MySQL中的JOIN语句是数据库操作中的重要组成部分，它允许我们基于两个或更多表之间的关联字段来合并数据。本文将深入探讨JOIN的基本使用方法以及字段字符集编码如何影响性能。 JOIN语句的基础在于它能根据两个表中...
mysql 开发技巧之JOIN 更新和数据查重/去重
09-10
为了解决这个问题，我们可以使用JOIN结构，将更新的表与包含筛选条件的子查询JOIN在一起。例如，假设我们有两张表t1和t2，且存在col_a字段的重复值，我们想要将所有重复的col_a字段设置为'hi'，可以这样写： ```sql...
软件测试|深入理解SQL FULL JOIN：语法、用法及示例解析
最新发布
Tester_muller的博客
07-17 738
在SQL中，JOIN是一个强大的操作，它允许将两个或多个表中的数据进行关联。SQL提供了多种JOIN类型，其中之一是FULL JOIN。FULL JOIN允许从左表和右表中选择所有记录，并将它们组合在一起。本文将深入探讨SQLFULL JOIN的语法、用法，并通过实例解析来说明其作用。通过本文的介绍，我们深入了解了SQL FULL JOIN的语法和工作原理。FULL JOIN对于合并数据和数据比对非常有用。在实际应用中，FULL JOIN是一个强大的工具，它可以帮助我们发现数据之间的关系以及存在的差异。
hive中多表full join主键重复问题
yang_shibiao的博客
01-24 5909
目录 0. 其他 1. 问题描述 2. 问题复现 2.1. 建表语句 2.2. 插入数据 2.3. 查询SQL以及问题 3. 问题原因 4. 问题解决 0. 相关文章链接 开发随笔文章汇总 1. 问题描述 在Hive中（其他类似SQL，比如PostgreSQL可能也存在此问题），当对多张表（3张及以上）进行full join时，会存在每张表的主键都是唯一，但当full join后，会发现主键可能有重复。 2. 问题复现 2.1. 建表语句 create t...
MySQL涉及4张表的级联查询
RaySunWHUT
10-27 9552
inner join的使用 数据库表之间的级联查询使用“join … on …” 关键字。 级联join分为： join inner join left join right join full join 四种join查询的区别： JOIN: 如果表中有至少一个匹配，则返回行； INNER JOIN：在表中存在至少一个匹配时，返回行。 LEFT JOIN: 即使右表中没有匹配，也从左表返回所...
【Oracle】深入多表连接
会飞的大象
06-08 2136
【Oracle】使用表连接时的一些思考
MySQL多表查询内连接外连接详解，join、left join、right join、full join的使用
睡竹的博客
04-10 7762
MySQL多表查询内连接外连接详解，join、left join、right join、full join、union、union all的使用及案例
Hive-SQL中的多表Full join
Samooyou的博客
11-02 1369
思路：Full join + coalesec()的使用 select coalesce(t1.id,t2.id,t3.id) id, t1.C1 C1, t2.C2 C2, t3.C3 C3 from t1 full join t2 on t1.id=t2.id full join t3 on coalesce(t1.id,t2.id)=t3.id;
三个表的全连接查询（多表联合查询）：替代full join
firstbird的博客
05-30 8671
多表联合查询
mysql 如何实现多列full join
03-28
MySQL 不支持多列 full join，但可以通过多个 left join 和 union 实现： 例如，假设有三个表 A、B、C，要执行 A.full join B on A.id=B.id and A.name=B.name.full join C on A.id=C.id and A.name=C.name，则可以...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值