Types of JOINs in SQL - Santhi's Blog

A join in SQL is a statement that combines data from one or two tables based on common field or matching condition. In this article, we will see the different type of JOINs in SQL with examples.

Types of JOINs:

• Inner JOIN
• Outer JOIN
  • Left Outer JOIN
  • Right Outer JOIN
  • Full Outer JOIN
• CROSS JOIN
• Self-JOIN, Natural JOIN, Equi JOIN

Inner JOIN:

• Returns records that matches with the both the tables

Example:

Student Table:

StudentID	StudentName
1	Raj
2	Nanda
3	Arjun

Course Table:

CourseID	CourseName	StudentID
C1	Maths	1
C2	English	1
C3	Hindi	Null

SELECT Student. StudentID, Student. StudentName, Course. CourseName FROM Student INNER JOIN ON Course WHERE Student. StudentID = Course. StudentID

Result:

StudentID	StudentName	CourseName
1	Raj	Maths
1	Raj	English

Outer JOIN:

Left Outer JOIN :

• Returns records that matches with the right table and all records from the left table even if there is not match in the right table

Example:

SELECT Student. StudentID, Student. StudentName, Course. CourseName FROM Student LEFT OUTER JOIN ON Course WHERE Student. StudentID = Course. StudentID

StudentID	StudentName	CourseName
1	Raj	Maths
1	Raj	English
2	Nanda	Null
3	Arjun	Null

Right Outer JOIN:

• Returns records that matches with the left table and all records from right table even though there is no match in the left table

Example:

SELECT Student. StudentID, Student. StudentName, Course. CourseName FROM Student RIGHT OUTER JOIN ON Course WHERE Student. StudentID = Course. StudentID

StudentID	StudentName	CourseName
1	Raj	Maths
1	Raj	English
Null	Null	Hindi

Full Outer JOIN:

• Returns all records that matches with the left table or from right table

Example:

SELECT Student. StudentID, Student. StudentName, Course. CourseName FROM Student FULL OUTER JOIN ON Course WHERE Student. StudentID = Course. StudentID

StudentID	StudentName	CourseName
1	Raj	Maths
1	Raj	English
Null	Null	Hindi
2	Nanda	Null
3	Arjun	Null

Cross JOIN:

• Returns cartesian product of all rows from the tables

Example:

SELECT Student. StudentID, Student. StudentName, Course. CourseID, Course. CourseName FROM Student CROSS JOIN Course;

StudentID	StudentName	CourseID	CourseName
1	Raj	C1	Maths
2	Nanda	C1	Maths
3	Arjun	C1	Maths
1	Raj	C2	English
2	Nanda	C2	English
3	Arjun	C2	English
1	Raj	C3	Hindi
2	Nanda	C3	Hindi
3	Arjun	C3	Hindi

Natural JOIN:

• Return the records that joins the tables based on the same columns and same datatypes
• It returns the unique columns

Example:

SELECT Student. StudentName, Course. CourseName FROM Student NATURAL JOIN  Course

StudentName	CourseName
Raj	Maths
Raj	English

Self JOIN:

• Joins the table with itself and returns the matching records

Example:

StudentID	StudentName	Hobbies
1	Raj	Swimming
2	Nanda	Swimming
3	Arjun	Football
4	Vidya	Swimming

SELECT  S1. StudentName as StudentName1, S2.StudentName as StudentName2, S1.Hobbies FROM Student S1, Student S2  WHERE  S1.StudentName <> S2.StudentName AND S1.Hobbies = S2.Hobbies

StudentName1	StudentName2	Hobbies
Raj	Nanda	Swimming
Nanda	Raj	Swimming
Vidya	Raj	Swimming
Raj	Vidya	Swimming
Nanda	Vidya	Swimming
Vidya	Nanda	Swimming

Equi JOIN:

• Joins the table based on equality condition of the columns

Example:

SELECT Student. StudentID, Student. StudentName, Course. CourseName FROM Student, Course WHERE Student. StudentID = Course. StudentID

StudentID	StudentName	CourseID	CourseName
1	Raj	C1	Maths
1	Raj	C2	English