Introduction to SQL Joins (Inner, Left, Right and Full Join)
SQL joins are fundamental tools for combining data from multiple tables
in relational databases.
For example, consider two tables where one table (say Student) has
student information with id as a key and other table (say Marks) has
information about marks of every student id. Now to display the marks
of every student with name, we need to join the two tables.
Please remember, we store data into multiple tables as part of database
normalization to avoid anomalies and redundancies.
Introduction to SQL Joins, Dr. Asoke Nath 1
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Types of SQL Joins
Let us visualize how each join type operates:
1. SQL INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the condition
is satisfied. This keyword will create the result set by combining all rows from both the
tables where the condition satisfies i.e value of the common field will be the same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER
JOIN table2 ON table1.matching_column = table2.matching_column;
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER
JOIN.
Introduction to SQL Joins, Dr. Asoke Nath 2
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Types of SQL Joins
Inner Join
Introduction to SQL Joins, Dr. Asoke Nath 3
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Types of SQL Joins
Example of INNER JOIN
Consider the two tables, Student and StudentCourse, which
share a common column ROLL_NO. Using SQL JOINS, we can
combine data from these tables based on their relationship,
allowing us to retrieve meaningful information like student
details along with their enrolled courses.
Introduction to SQL Joins, Dr. Asoke Nath 4
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Types of SQL Joins
Example of INNER JOIN
1. Student Table: 2. StudentCourse Table:
ROLL_N COURSE_ID ROLL_NO
O NAME ADDRESS PHONE AGE
1
1
HARSH DELHI XXXXXXXXXX 18
1 2
2
PRATIK BIHAR XXXXXXXXXX 19 3
2 2
RIYANKA SILIGURI XXXXXXXXXX 20 4
3
3
DEEP RAMNAGAR XXXXXXXXXX 18
4
Introduction to SQL Joins, Dr. Asoke Nath 5
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Example of INNER JOIN(contd..)
Let's look at the example of INNER JOIN clause, and understand it's working. This query will show the
names and age of students enrolled in different courses.
Query:
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Introduction to SQL Joins, Dr. Asoke Nath 6
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Example of INNER JOIN(contd..)
Output:
COURSE_ID NAME AGE
HARSH 18
1
PRATIK 19
2
RIYANKA 20
2
DEEP 18
3
SQL LEFT JOIN
A LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If there is no
match, NULL values are returned for columns from the right table. LEFT JOIN is also known as LEFT OUTER
JOIN.
Syntax
SELECT table1.column1,table1.column2,table2.column1,.... FROM table1
LEFT JOIN table2 ON table1.matching_column = table2.matching_column;
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.
Introduction to SQL Joins, Dr. Asoke Nath 7
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Example of LEFT JOIN(contd..)
Left JOIN
LEFT JOIN Example
In this example, the LEFT JOIN retrieves all rows from the Student table and the matching rows from the
StudentCourse table based on the ROLL_NOcolumn.
Query:
SELECT Student.NAME,StudentCourse.COURSE_ID FROM Student LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Introduction to SQL Joins, Dr. Asoke Nath 8
Introduction to SQL Joins (Inner, Left, Right and Full Join)
Example of LEFT JOIN(contd..)
Left JOIN Output:
NAME COURSE_ID
1
HARSH
2
PRATIK
2
RIYANKA
3
DEEP
Introduction to SQL Joins, Dr. Asoke Nath 9
Introduction to SQL Joins (Inner, Left, Right and Full Join)
3. SQL RIGHT JOIN
RIGHT 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 the join. It is very similar to LEFT JOIN for the rows for which there
is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as
RIGHT OUTER JOIN.
Syntax
SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 RIGHT JOIN
table2
ON table1.matching_column = table2.matching_column;
Key Terms
1) table1: First table.
2) table2: Second table
3) matching_column: Column common to both the tables.
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same
Introduction to SQL Joins, Dr. Asoke Nath 10
Introduction to SQL Joins (Inner, Left, Right and Full Join)
3. SQL RIGHT JOIN
Right JOIN:
RIGHT JOIN Example
In this example, the RIGHT JOIN retrieves all rows from the StudentCourse table and the matching
rows from the Student table based on the ROLL_NO column.
Query:
SELECT Student.NAME,StudentCourse.COURSE_ID FROM Student RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Introduction to SQL Joins, Dr. Asoke Nath 11
Introduction to SQL Joins (Inner, Left, Right and Full Join)
3. SQL RIGHT JOIN(contd..)
NAME COURSE_ID
1
HARSH
2
PRATIK
2
RIYANKA
3
DEEP
4
NULL
Introduction to SQL Joins, Dr. Asoke Nath 12
Introduction to SQL Joins (Inner, Left, Right and Full Join)
4. SQL FULL JOIN:
FULL JOIN creates the result-set by combining results of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both tables. For the
rows for which there is no matching, the result-set will contain NULL values.
Introduction to SQL Joins, Dr. Asoke Nath 13
Introduction to SQL Joins (Inner, Left, Right and Full Join)
4. SQL FULL JOIN(contd..):
Syntax
SELECT table1.column1,table1.column2,table2.column1,....FROM table1
FULL JOIN table2 ON table1.matching_column = table2.matching_column;
Key Terms
1) table1: First table.
2) table2: Second table
3) matching_column: Column common to both the tables.
Introduction to SQL Joins, Dr. Asoke Nath 14
Introduction to SQL Joins (Inner, Left, Right and Full Join)
4. SQL FULL JOIN(contd..):
FULL JOIN Example
This example demonstrates the use of a FULL JOIN, which combines the results of both LEFT JOIN
and RIGHT JOIN. The query retrieves all rows from the Student and StudentCourse tables. If a record
in one table does not have a matching record in the other table, the result set will include that record
with NULL values for the missing fields
Query:
SELECT Student.NAME,StudentCourse.COURSE_ID FROM Student FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Introduction to SQL Joins, Dr. Asoke Nath 15
Introduction to SQL Joins (Inner, Left, Right and Full Join)
4. SQL FULL JOIN(contd..):
OUTPUT:
NAME COURSE_ID
HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
Introduction to SQL Joins, Dr. Asoke Nath 16
Introduction to SQL Joins (Inner, Left, Right and Full Join)
5. SQL Natural Join
A Natural Join is a type of INNER JOIN that automatically joins two tables based
on columns with the same name and data type. It returns only the rows where the
values in the common columns match.
1) It returns rows where the values in these common columns are the same in both
tables.
2) Common columns appear only once in the result, even if they exist in both
tables.
3) Unlike a CROSS JOIN, which creates all possible combinations of rows, a
Natural Join only includes rows with matching values
Introduction to SQL Joins, Dr. Asoke Nath 17
Introduction to SQL Joins (Inner, Left, Right and Full Join)
5. SQL Natural Join(contd..)
Example:
Look at the two tables below: Employee and Department
Employee Department
Emp_id Emp_name Dept_id Dept_id Dept_name
10 IT
1 Ram 10
2 Jon 30 30 HR
3 Bob 50 40 TIS
Find all Employees and their respective departments.
(Employee) ? (Department)
Introduction to SQL Joins, Dr. Asoke Nath 18
Introduction to SQL Joins (Inner, Left, Right and Full Join)
5. SQL Natural Join(contd..)
Output:
Emp_id Emp_name Dept_id Dept_id Dept_name
1 Ram 10 10 IT
2 Jon 30 30 HR
Introduction to SQL Joins, Dr. Asoke Nath 19
