Explain mysql left join please

  Kiến thức lập trình

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.

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT