Mysql Join
(C) by Dennis DLL 2004.1.29
Still now CREATE TABLE
Create Table EMP (ID INT Not Null Primary Key, Name Varchar (10));
CREATE TABLE EMP_DEPT (de PEPT_ID VARCHAR (4) Not null, EMP_ID INT NOT NULL, EMP_NAME VARCHAR (10), PRIMARY Key (de PEPT_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_IDFROM EMP A LEFT JOIN EMP_DEPT B On (A.ID = B.EMP_ID);
# Pick all the information in the Table EMP on the left, even if the information in Emp_DEPT is also picked out, there is no NULL to display, and the display information is based on the data in the Table EMP on the left.
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 | ---- ------- ---- ---------
# 出 资 资 资 资 资 资;;;;;;;;;;;;;;
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 | ---- ----------- ------- # put the Table Emp_DEPT on the left side (of course, the data is displayed based on the data in EMP_DEPT, There will be no more information in EMP than EMP_DEPT):
Select A.ID, A.Name, B.DEPT_IDFROM 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_IDFROM EMP A Right Join Emp_Dept B On (A.ID = B.Emp_ID); # Data is based on the information in the right Table Emp_DEPT.
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)
# 我们 我们 把 的 的,, then try Right Join
Select A.ID, A.Name, B.DEPT_IDFROM 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 like LEFT JOIN?
>> Direct Join -------------- # If you use Right Join, you don't have to directly pick information as the same, which is the following instructions.
Select A.ID, A.Name, B.DEPT_IDFROM 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 | ---- --- ----- ---------
What, do you understand?
Enjoy it!
