MySQL JOIN keyword is used to query data from two or more related tables. We can use JOINS in SELECT, UPDATE and DELETE statements to JOIN MySQL tables.
To understand JOINs first create related tables.
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(30) NOT NULL,
`course_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
foreign key(`course_id`) references course(`id`)
) ENGINE=InnoDB;
Now We are considering that course and student tables have following data.
Course table| id | course_name |
|---|---|
| 1 | JSON |
| 2 | HTACCESS |
| 3 | MYSQL |
| 4 | CODEIGNITER |
| 5 | JQUERY |
| id | student_name | course_id |
|---|---|---|
| 1 | Manish | 1 |
| 2 | Bunny | 1 |
| 3 | Mary | 2 |
| 4 | Bob | 5 |
| 5 | Pawan | (NULL) |
INNER JOIN produces a record that present in both the course and student tables. means its show all student who are enrolled on a course.
SELECT student.student_name, course.course_name
FROM `student`
INNER JOIN `course` on student.course_id = course.id;
| student.student_name | course.course_name |
|---|---|
| Manish | JSON |
| Bunny | JSON |
| Mary | HTACCESS |
| Bob | JQUERY |
LEFT JOIN produces a record that matches every entry in the left table(student) regardless of any matching entry in the right table (course).The result is NULL in the right side when there is no match.
SELECT student.student_name, course.course_name
FROM `student` LEFT JOIN `course` on student.course_id = course.id;
| student.student_name | course.course_name |
|---|---|
| Manish | JSON |
| Bunny | JSON |
| Mary | HTACCESS |
| Bob | JQUERY |
| Pawan | (NULL) |
RIGHT JOIN produces a record that matches every entry in the right table (course) regardless of any matching entry in the left table (student).The result is NULL in the left side when there is no match.
SELECT student.student_name, course.course_name
FROM `student` RIGHT JOIN `course` on student.course_id = course.id;
| student.student_name | course.course_name |
|---|---|
| Manish | JSON |
| Bunny | JSON |
| Mary | HTACCESS |
| (NULL) | MYSQL |
| (NULL) | CODEIGNITER |
| Bob | JQUERY |
OUTER JOIN produces a record from both tables regardless of any match. The result is NULL comes, when there is no match. OUTER JOIN is not implemented in MySQL. However ,we can achieve using the UNION of a LEFT and RIGHT JOIN.
SELECT student.student_name, course.course_name
FROM `student` LEFT JOIN `course` on student.course_id = course.id
UNION
SELECT student.student_name, course.course_name
FROM `student` RIGHT JOIN `course` on student.course_id = course.id
| student.student_name | course.course_name |
|---|---|
| Manish | JSON |
| Bunny | JSON |
| Mary | HTACCESS |
| Bob | JQUERY |
| Pawan | (NULL) |
| (NULL) | MYSQL |
| (NULL) | CODEIGNITER |
