SQL JOINS - A Refresher

Quick Intro

There are four basic types of SQL joins: INNER, LEFT, RIGHT, and FULL. An intuitive way to explain the difference between these four types is by using a Venn diagram which shows logical relations between data sets.

A Venn Diagram Illustrating SQL Joins

Lets explain the join types by applying them to some data.

Table name: student

Table name: student_course

INNER JOIN

Inner join aka join Returns all rows from both tables as long as the condition satisfies i.e value of the common field will be same.

SELECT student_course.course_name, student.student_name FROM student
INNER JOIN student_course ON student.roll_number = student_course.roll_number;

LEFT JOIN

Left join aka left outer join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on the right side, the result-set will contain NULL.

SELECT student_course.course_name, student.student_name FROM student
LEFT JOIN student_course ON student.roll_number = student_course.roll_number;

RIGHT JOIN

Right join aka right outer join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain NULL.

SELECT student_course.course_name, student.student_name FROM student
RIGHT JOIN student_course ON student.roll_number = student_course.roll_number;

FULL JOIN

Full join aka full outer join returns the result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL.

SELECT student_course.course_name, student.student_name FROM student
FULL JOIN student_course ON student.roll_number = student_course.roll_number;