MySQL join solution

Source of Dennis dll: csdn

 

Create Table first

 

Create Table EMP (
Id int not null primary key,
Name varchar (10)
);

 

Create Table emp_dept (
Dept_id varchar (4) not null,
Emp_id int not null,
Emp_name varchar (10 ),
Primary Key (dept_id, emp_id ));

Insert into EMP () Values
(1, "Dennis-1 "),
(2, "Dennis-2 "),
(3, "Dennis-3 "),
(4, "Dennis-4 "),
(5, "Dennis-5 "),
(6, "Dennis-6 "),
(7, "Dennis-7 "),
(8, "Dennis-8 "),
(9, "Dennis-9 "),
(10, "Dennis-10 ");

Insert into emp_dept () Values
("R & D", 1, "Dennis-1 "),
("Dev", 2, "Dennis-2 "),
("R & D", 3, "Dennis-3 "),
("Test", 4, "Dennis-4 "),
("Test", 5, "Dennis-5 ");

 

> Left join
-------------
Select a. ID, A. Name, B. dept_id
From EMP a left join emp_dept B on (A. ID = B. emp_id );

 

# Picking all the data in Table EMP of the Left partition, even if there is no data in emp_dept, null is used to show none,
# That is, the metadata is based on the data in the table EMP of the left region.

 

Mysql> select a. ID, A. Name, B. dept_id
-> From EMP a left join emp_dept B on (A. ID = B. emp_id );
+ ---- + ----------- + --------- +
| ID | Name | dept_id |
+ ---- + ----------- + --------- +
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | Dev |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | test |
| 5 | Dennis-5 | test |
| 6 | Dennis-6 | null |
| 7 | Dennis-7 | null |
| 8 | Dennis-8 | null |
| 9 | Dennis-9 | null |
| 10 | Dennis-10 | null |
+ ---- + ----------- + --------- +

 

 

# Picking out the employees in Table EMP and not in Table emp_dept

Select a. ID, A. Name, B. dept_id
From EMP a left join emp_dept B on (A. ID = B. emp_id)
Where B. dept_id is null;

 

Mysql> select a. ID, A. Name, B. dept_id
-> From EMP a left join emp_dept B on (A. ID = B. emp_id)
-> Where B. dept_id is null;
+ ---- + ----------- + --------- +
| ID | Name | dept_id |
+ ---- + ----------- + --------- +
| 6 | Dennis-6 | null |
| 7 | Dennis-7 | null |
| 8 | Dennis-8 | null |
| 9 | Dennis-9 | null |
| 10 | Dennis-10 | null |
+ ---- + ----------- + --------- +

 

# Place the table emp_dept on the left partition (of course, the data in emp_dept is used as the base partition to display the data, more information in EMP than emp_dept will not be displayed ):

 

Select a. ID, A. Name, B. dept_id
From emp_dept B left join emp a on (A. ID = B. emp_id );

Mysql> select a. ID, A. Name, B. dept_id
-> From emp_dept B left join emp a on (A. ID = B. emp_id );
+ ------ + ---------- + --------- +
| ID | Name | dept_id |
+ ------ + ---------- + --------- +
| 2 | Dennis-2 | Dev |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | test |
| 5 | Dennis-5 | test |
+ ------ + ---------- + --------- +

 

 

> Right join
---------------
Select a. ID, A. Name, B. dept_id
From EMP a right join emp_dept B on (A. ID = B. emp_id );

# When selecting data, use the data in the right outer table emp_dept as the basis to display data

 

Mysql> select a. ID, A. Name, B. dept_id
-> From EMP a right join emp_dept B on (A. ID = B. emp_id );
+ ------ + ---------- + --------- +
| ID | Name | dept_id |
+ ------ + ---------- + --------- +
| 2 | Dennis-2 | Dev |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | test |
| 5 | Dennis-5 | test |
+ ------ + ---------- + --------- +
5 rows in SET (0.00 Sec)

 

# We will submit the table position again, and then use right join to continue reading

 

Select a. ID, A. Name, B. dept_id
From emp_dept B right join emp a on (A. ID = B. emp_id );

 

Mysql> select a. ID, A. Name, B. dept_id
-> From emp_dept B right join emp a on (A. ID = B. emp_id );
+ ---- + ----------- + --------- +
| ID | Name | dept_id |
+ ---- + ----------- + --------- +
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | Dev |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | test |
| 5 | Dennis-5 | test |
| 6 | Dennis-6 | null |
| 7 | Dennis-7 | null |
| 8 | Dennis-8 | null |
| 9 | Dennis-9 | null |
| 10 | Dennis-10 | null |
+ ---- + ----------- + --------- +

 

# Is it the same as left join?

 

> Direct join
--------------
# If right join is the same as directly picking data without join, it describes the following commands.

 

Select a. ID, A. Name, B. dept_id
From emp a, emp_dept B
Where a. ID = B. emp_id;

Mysql> select a. ID, A. Name, B. dept_id
-> From emp a, emp_dept B
-> Where a. ID = B. emp_id;
+ ---- + ---------- + --------- +
| ID | Name | dept_id |
+ ---- + ---------- + --------- +
| 2 | Dennis-2 | Dev |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | test |
| 5 | Dennis-5 | test |
+ ---- + ---------- + --------- +

How can this problem be solved?

Enjoy it!