Learn MySQL

MySQL JOINs

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.

Types of MySQL Joins

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN

Related 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
Student table
id student_name course_id
1 Manish 1
2 Bunny 1
3 Mary 2
4 Bob 5
5 Pawan (NULL)

INNER JOIN

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

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

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

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