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');
select *
from tab1 t1
left join tab2 t2 on t1.field11 = t2.field21;
I got the following result:
+-----+---------+------------+------+---------+------------+
| 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 expect following result:
+-----+---------+------------+------+---------+------------+
| 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 documentation (from 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).
The results dataset must be only records that correspond to ONLY the green circle on the picture.
How does this left join work?
t1.field11 = t2.field21, and that is what was returned. If you look at theidfields of each returned entry you can see that the matching pairs are all unique.on t1.field11 = t2.field21- For each record intab1with afield11value of11there exist three records intab2with a matchingfield21value. What you're seeing is the cartesian product of that join. Three records on the left, each with three matching records on the right, equals nine records. Plus the remaining three records on the left with no matching records on the right. Your expectation was simply wrong.field11has a row with value11.field21has three rows with value11. The one row offield11gets joined to all three because your database has no idea which one of the three rows infield21YOU would like to join. You'll have to be more specific in your join criteria to get to your output. It's VERY important to understand that a table has no inherent ordering. The order you see as a human means nothing. Perhaps you SHOULD be joiningid1toid2if you want that ouput.on t1.field11 = t2.field21 AND t1.id1 = t2.id2?