EN
MySQL - Left Join
0
points
In this article, we would like to show you how to use LEFT JOIN in MySQL.
Quick solution:
SELECT `column1`, `column2`, `columnN`
FROM `table1`
LEFT JOIN `table2`
ON `table1`.`column_name` = `table2`.`column_name`;
![LEFT JOIN in MySQL](/static/bucket/1628515127277-N9QPbE1Dlq--image.png)
Practical example
To show how the LEFT JOIN
works, we will use the following tables:
![Tables in the database](/static/bucket/1628515453475-yjQ7GyoQeL--image.png)
Note:
At the end of this article you can find databases preparation SQL queries.
Example
In this example, we will select all users with department information.
Query:
SELECT * FROM `users`
LEFT JOIN `departments`
ON `departments`.`id` = `users`.`department_id`;
Output:
![MySQL - LEFT JOIN result - HeidiSQL](https://dirask.com/static/bucket/1617738815914-XLMYZb3Ye8--image.png)
Database preparation
create_tables.sql
file:
CREATE TABLE `departments` (
`id` INT(10) UNSIGNED NOT NULL,
`department_name` VARCHAR(50) NOT NULL,
`location` VARCHAR(50) NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`surname` VARCHAR(50) NOT NULL,
`department_id` INT(10) UNSIGNED,
PRIMARY KEY (`id`),
FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)
)
insert_data.sql
file:
INSERT INTO `departments`
(`id`, `department_name`, `location`)
VALUES
(1, 'Sales', 'New York'),
(2, 'Finance', NULL),
(3, 'HR', 'Atlanta'),
(4, 'Purchase', 'New Orlean'),
(5, 'Operations', 'Boston');
INSERT INTO `users`
( `name`, `surname`, `department_id`)
VALUES
('John', 'Stewart', 1),
('Chris', 'Brown', 2),
('Kate', 'Lewis', 3),
('Ailisa', 'Gomez', NULL),
('Gwendolyn', 'James', 2),
('Simon', 'Collins', 4),
('Taylor', 'Martin', 2),
('Andrew', 'Thompson', NULL);