MySQL Full Outer Join

In mysql full outer join, Best Example The SQL FULL JOIN combines the results of both left as well as right outer joins. Also MySQL does not support full outer join out of the unit Also You can learn to Using Five Types of JOIN in MySQL.

Contents

with two tables first_tbl, second_tbl:

SELECT * FROM first_tbl
LEFT JOIN second_tbl ON first_tbl.id = second_tbl.id
UNION
SELECT * FROM first_tbl
RIGHT JOIN second_tbl ON first_tbl.id = second_tbl.id

What is Full Outer Join in SQL?

In mysql full outer join combines or merge the results of both left as well as right outer joins with returns all (matched or unmatched) rows from the database tables on both sides of the main joins sql clause.

SQL Code:

SELECT * FROM business 
FULL OUTER JOIN members 
ON business.B=members.B;

mysql group by multiple columns
Example

SELECT a.business_id AS "a.Bui_id",
a.business_name AS "C_Name",
b.business_id AS "b.Bui_id", 
b.item_name AS "I_Name" 
FROM   business a 
FULL OUTER JOIN foods b 
ON a.business_id = b.business_id;

FULL OUTER JOIN using WHERE clause

SELECT a.business_id AS "a.Bui_id", 
a.business_name AS "C_Name",
b.business_id AS "b.Bui_id", 
b.item_name AS "I_Name" 
FROM   business a
FULL OUTER JOIN foods b
ON a.business_id = b.business_id
WHERE a.business_id IS NULL 
OR b.business_id IS NULL 
ORDER BY business_name;

FULL OUTER JOIN using UNION clause

SELECT business.A,business.M,members.A,members.N 
FROM business 
FULL OUTER JOIN members 
ON business.a=members.A 
ORDER BY business.A;

FULL OUTER JOIN using LEFT combination RIGHT OUTER JOIN and UNION clause

SELECT business.A,business.M,members.A,members.N 
FROM business 
LEFT OUTER JOIN members 
ON business.a=members.A 
UNION 
SELECT business.A,business.M,members.A,members.N 
FROM business 
RIGHT OUTER JOIN members 
ON business.a=members.A;

How to Simulate mysql full outer join

left join:

select * from members as a
    left outer join boss as o on a.age = o.age

right join:

select * from members as a
    right outer join boss as o on a.age = o.age

FULL OUTER JOIN

create table members (occupation char(10) not null primary key, age int not null);
create table boss (occupation char(10) not null primary key, age int not null);
insert into members(occupation, age) values('ravi',5),('Kajal',6);
insert into boss(occupation, age) values('preamdeep',4),('Naval',5);

two JOINs and a UNION

select * from members as a
    left outer join boss as o on a.age = o.age
union
select * from members as a
    right outer join boss as o on a.age = o.age

UNION ALL and an exclusion join

select * from members as a
   left outer join boss as o on a.age = o.age
union all
select * from members as a
   right outer join boss as o on a.age = o.age
where a.age is null;

Don’t Miss : SQL Check If Table Exists

Full Outer Join Example

Setting up the sample data in mysql

-- ----------------------------
-- Table structure for `bosss`
-- ----------------------------
DROP TABLE IF EXISTS `bosss`;
CREATE TABLE `bosss` (
`boss_id` int(11) NOT NULL AUTO_INCREMENT,
`boss` varchar(30) DEFAULT NULL,
PRIMARY KEY (`boss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of bosss
-- ----------------------------
INSERT INTO `bosss` VALUES ('1', 'Rejha');
INSERT INTO `bosss` VALUES ('2', 'Kajal');
INSERT INTO `bosss` VALUES ('3', 'Bhoomi');
INSERT INTO `bosss` VALUES ('6', 'Hitesh');
INSERT INTO `bosss` VALUES ('9', 'Sheela');
-- ----------------------------
-- Table structure for `tricks`
-- ----------------------------
DROP TABLE IF EXISTS `tricks`;
CREATE TABLE `tricks` (
`trick_id` int(11) NOT NULL AUTO_INCREMENT,
`trick` varchar(30) DEFAULT NULL,
`boss_id` int(11) DEFAULT NULL,
PRIMARY KEY (`trick_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tricks
-- ----------------------------
INSERT INTO `tricks` VALUES ('1', 'Rejha', '9');
INSERT INTO `tricks` VALUES ('2', 'syam', '1');
INSERT INTO `tricks` VALUES ('3', 'Kajal', '1');
INSERT INTO `tricks` VALUES ('4', 'Dipti', '2');
INSERT INTO `tricks` VALUES ('5', 'Mayur', '1');
INSERT INTO `tricks` VALUES ('6', 'Hitesh', null);
INSERT INTO `tricks` VALUES ('7', 'Bhavika', null);
INSERT INTO `tricks` VALUES ('8', 'Deepa Raghav', '9');
INSERT INTO `tricks` VALUES ('9', 'Keshu', null);
INSERT INTO `tricks` VALUES ('10', 'Madhuri', null);

SELECT `bosss`.`boss`, tricks.trick
FROM `bosss`
LEFT JOIN `tricks` ON `bosss`.`boss_id` = `tricks`.`boss_id`
UNION ALL
SELECT `bosss`.`boss`, tricks.trick
FROM `bosss`
RIGHT JOIN `tricks` ON `bosss`.`boss_id` = `tricks`.`boss_id`
WHERE `bosss`.`boss_id` IS NULL;

Inner join in MySQL

SELECT 
	members.mem_fname, companies.Compny_id, companies.Compny_date 
FROM 
	members INNER JOIN companies 
	ON member.mem_id = companies.mem_id;

Outer join in MySQL

Left-outer join in MySQL

SELECT 
	members.mem_fname, companies.Compny_id, companies.Compny_date 
FROM 
	members LEFT OUTER JOIN companies 
ON member.mem_id = companies.mem_id;

Right-outer join in MySQL

SELECT 
	members.mem_fname, companies.Compny_id, companies.Compny_date 
FROM 
	members RIGHT OUTER JOIN companies 
ON member.mem_id = companies.mem_id;

Union in MySQL

SELECT 
	members.mem_fname, members.mem_lname
FROM 
	members

UNION

SELECT 
	visitors.visit_fname, visitors.visit_lname
FROM 
	visitors

Intersection in MySQL

SELECT 
	DISTINCT members.mem_id
FROM 
	members
WHERE
	members.mem_id 
IN (SELECT mem_id FROM companies)

//using INNER JOIN


SELECT 
	DISTINCT members.mem_id
FROM 
	members
INNER JOIN
	compa USING(mem_id);

Ref : https://dev.mysql.com/doc/refman/8.0/en/join.html

I hope you get an idea about mysql full outer join.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

MySQL full outer join – BEST 5 WAYS TO “mysql full outer join”