Explain me mysql left join behaviour please. I have simple example dataset with two tables:
CREATE TABLE tab1 (
id1 int NOT NULL,
field11 int NOT NULL,
field12 date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO tab1 (id1, field11, field12) VALUES
(1, 11, '2024-07-10'),
(2, 11, '2024-07-10'),
(3, 11, '2024-07-10'),
(4, 12, '2024-07-11'),
(5, 12, '2024-07-11'),
(6, 12, '2024-07-11');
CREATE TABLE tab2 (
id2 int NOT NULL,
field21 int NOT NULL,
field22 date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO tab2 (id2, field21, field22) VALUES
(1, 11, '2024-07-01'),
(2, 11, '2024-07-01'),
(3, 11, '2024-07-01'),
(4, 22, '2024-07-02'),
(5, 22, '2024-07-02'),
(6, 22, '2024-07-02');
I have query:
select * from tab1 t1
left join tab2 t2 on t1.field11 = t2.field21;
I got followed results:
+-----+---------+------------+------+---------+------------+
| id1 | field11 | field12 | id2 | field21 | field22 |
+-----+---------+------------+------+---------+------------+
| 1 | 11 | 2024-07-10 | 3 | 11 | 2024-07-01 |
| 1 | 11 | 2024-07-10 | 2 | 11 | 2024-07-01 |
| 1 | 11 | 2024-07-10 | 1 | 11 | 2024-07-01 |
| 2 | 11 | 2024-07-10 | 3 | 11 | 2024-07-01 |
| 2 | 11 | 2024-07-10 | 2 | 11 | 2024-07-01 |
| 2 | 11 | 2024-07-10 | 1 | 11 | 2024-07-01 |
| 3 | 11 | 2024-07-10 | 3 | 11 | 2024-07-01 |
| 3 | 11 | 2024-07-10 | 2 | 11 | 2024-07-01 |
| 3 | 11 | 2024-07-10 | 1 | 11 | 2024-07-01 |
| 4 | 12 | 2024-07-11 | NULL | NULL | NULL |
| 5 | 12 | 2024-07-11 | NULL | NULL | NULL |
| 6 | 12 | 2024-07-11 | NULL | NULL | NULL |
+-----+---------+------------+------+---------+------------+
12 rows in set (0.00 sec)
But I’m expect followed results:
+-----+---------+------------+------+---------+------------+
| id1 | field11 | field12 | id2 | field21 | field22 |
+-----+---------+------------+------+---------+------------+
| 1 | 11 | 2024-07-10 | 1 | 11 | 2024-07-01 |
| 2 | 11 | 2024-07-10 | 2 | 11 | 2024-07-01 |
| 3 | 11 | 2024-07-10 | 3 | 11 | 2024-07-01 |
| 4 | 12 | 2024-07-11 | NULL | NULL | NULL |
| 5 | 12 | 2024-07-11 | NULL | NULL | NULL |
| 6 | 12 | 2024-07-11 | NULL | NULL | NULL |
+-----+---------+------------+------+---------+------------+
My expectations are based on this one documentation (https://www.w3schools.com/mysql/mysql_join_left.asp):
The LEFT JOIN keyword returns all records from the left table
(table1), and the matching records (if any) from the right table
(table2).
As I understand in results dataset must be present only records that corresponds ONLY green circle on the picture.
Explain me please how does left join works.