MySQL Join Detailed Solution

Mysql
MySQL Join Detailed Solution

(C) by Dennis Dll 2004.1.29

Let's Create a 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"),
(a "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);

# pick out all the data in the table EMP on the left, even if there's no information in the emp_dept, it's not shown in NULL,
# also shows that the data is based on the data in the left table emp

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 |
+----+-----------+---------+

# Pick out the table in the EMP there's nobody in the 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 |
+----+-----------+---------+

# Put the table emp_dept on the left side (which, of course, is based on the data in the Emp_dept, and the EMP is not shown in more than emp_dept):

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);
# to show the data in the right table emp_dept on the basis of information

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)

# Let's change the table position and try the right join.

Select a.id,a.name,b.dept_id
From Emp_dept B right joins EMP A on (a.id=b.emp_id);

Mysql> Select a.id,a.name,b.dept_id
-> from Emp_dept b right joins 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 a left join?

>> Direct Join
--------------
# If the right join is the same without the join, it is the same as the following instruction

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 do you figure it out?

Enjoy it!