EN
MySQL - Full Outer Join
0 points
In this article, we would like to show you how to do FULL OUTER JOIN in MySQL.
Quick solution:
xxxxxxxxxx1
SELECT * FROM `table1`2
LEFT JOIN `table2` ON `table2`.`column_name` = `table1`.`column_name`3
UNION4
SELECT * FROM `table2`5
RIGHT JOIN `table2` ON `table2`.`column_name` = `table1`.`column_name`;To show how the FULL OUTER JOIN works, we will use the following tables:
Note:
At the end of this article you can find databases preparation SQL queries.
In this example, we will select all information about users and departments.
Query:
xxxxxxxxxx1
SELECT * 2
FROM `users` 3
LEFT JOIN `departments` ON `departments`.`id` = `users`.`department_id`4
UNION5
SELECT * 6
FROM `users` 7
RIGHT JOIN `departments` ON `departments`.`id` = `users`.`department_id`Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `departments` (2
`id` INT(10) UNSIGNED NOT NULL,3
`department_name` VARCHAR(50) NOT NULL,4
`location` VARCHAR(50) NULL,5
PRIMARY KEY (`id`)6
);7
8
CREATE TABLE `users` (9
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,10
`name` VARCHAR(50) NOT NULL,11
`surname` VARCHAR(50) NOT NULL,12
`department_id` INT(10) UNSIGNED,13
PRIMARY KEY (`id`),14
FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)15
)insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `departments`2
(`id`, `department_name`, `location`)3
VALUES4
(1, 'Sales', 'New York'),5
(2, 'Finance', NULL),6
(3, 'HR', 'Atlanta'),7
(4, 'Purchase', 'New Orlean'),8
(5, 'Operations', 'Boston');9
10
INSERT INTO `users`11
( `name`, `surname`, `department_id`)12
VALUES13
('John', 'Stewart', 1),14
('Chris', 'Brown', 2),15
('Kate', 'Lewis', 3),16
('Ailisa', 'Gomez', NULL),17
('Gwendolyn', 'James', 2),18
('Simon', 'Collins', 4),19
('Taylor', 'Martin', 2),20
('Andrew', 'Thompson', NULL);