The following topics will be covered in this tutorial:

Level up your SQL skills today with our video on SQL JOINs. Watch now!

What is JOIN in SQL?

As the term suggests, SQL JOINs refers to a clause in SQL that is used to combine rows of two or more tables by using common values. It takes into consideration the records from two or more tables in a database and combines them. SQL JOIN is typically used in situations where you want to access one or more tables through a select statement.

SQL JOIN Syntax

SQL JOIN ( also referred to as INNER JOIN ) is the most commonly used method of JOINing data from multiple tables. The syntax for SQL JOIN is given below:

Consider the example below, the syntax used to JOIN data from these tables will be:

SELECT Name, City, Marks, Phone_no
FROM Student_detail, Personal_detail
WHERE Student_details.Name=Personal_detail.Name;

Prepare yourself for the industry by going through these Top SQL Interview Questions and Answers!

SQL JOIN Example:

Now let us understand SQL JOIN with the help of a detailed example: 

Consider this table showing student details:

Name Roll No. Marks
Akshay 1 57
Charu 2 68
Disha 3 52
Eva 4 68
Himanshu 5 75
Jitesh 6 88

Below given is another table showing the personal details:

Name City Phone No Email Id
Akshay Jaipur 9543846521 [email protected]
Disha Bombay 8532465892 [email protected]
Jitesh Banglore 9684365125 [email protected]

Get 100% Hike!

Master Most in Demand Skills Now !

Now, by giving the following syntax, the two tables are JOINed together to form a new table that shows the name of the student, his/her city, marks obtained, and phone number.

SELECT Name, City, Marks, Phone_no
FROM Student_detail, Personal_detail
WHERE Student_details.Name=Personal_detail.Name;

The result of the above SQL JOIN syntax is given below:

Name City Marks Phone No
Akshay Jaipur 57 9543846521
Disha Bombay 52 8532465892
Jitesh Banglore 88 9684365125

Become a Database Architect

SQL JOIN Types

There are different types of JOINs available in SQL:

  • Inner JOIN in SQL
  • Left JOIN in SQL
  • Right JOIN in SQL
  • Full JOIN in SQL

Inner JOIN in SQL:

Inner SQL JOIN Query returns a value when there is a match in both tables. Inner JOIN is the most commonly used method when it comes to JOINing tables. The terms ‘Inner JOIN’ and ‘SQL JOIN’ are sometimes used interchangeably. Below given is the syntax for Inner JOIN. It is also the same syntax used in the case of SQL JOIN.

Syntax:

SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON Table1.ColName = Table2.ColName

Example: 

SELECT students_data.RollNo, students_data.Name, students_data.Address, students_mark.Marks, students_mark.Grade
FROM students_data
INNER JOIN students_mark ON students_data.RollNo = students_mark.RollNo;

Left JOIN in SQL:

Left SQL JOIN Query returns all the values of the left table and matches the rows from the right table. Now, if there is no matching value on the right side, Left JOIN will return the value from the right side as NULL. In most of the cases, the result from Left JOIN is mostly the same as one from Right JOIN except that all the rows from the left table are also included.

Syntax:

SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT Student_details.Name, Address, Roll_no, Email_id
FROM Student_details
LEFT JOIN Personal_details
ON Student_details.Name= Personal_details.Name;

Thinking of learning SQL? We have the perfect professional SQL Training Course for you!

Name Roll No. Address Email Id
Akshay 1 Jaipur [email protected]
Charu 2 NULL NULL
Disha 3 Bombay [email protected]
Eva 4 NULL NULL
Himanshu 5 NULL NULL
Jitesh 6 Banglore [email protected]

Career Transition

Got Job With Salary Hike | Process Developer to Sr. Software Engineer Career Transition
Got Multiple Job Offers with 100% Salary Hike | Best SQL Course | Intellipaat Career Transition
How To Switch Career From A Support Role To Oracle DBA Engineer | Got Job With 80% Salary Hike
How To Become A Database Expert From A Non Tech Background | Got Job With Salary Hike | Intellipaat
Got Job as Cloud DBA Just After completion of the Course | Intellipaat Career Transition
SQL Developer to Software Engineer Career Transition | Got Job with 120% Salary Hike | Intellipaat

Right JOIN in SQL:

Right SQL JOIN Query returns all the rows of the right table even if there are no matches in the left table. Basically, the Right JOIN will return all the values from the right table along with the matched values of the left table. Also, if there is no matched value found, the result will be NULL.

Syntax:

SELECT column_name
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT Personal_details.Name, Address, Roll_no, Email_id
FROM Student_details
RIGHT JOIN Personal_details

Come to Intellipaat’s SQL Community if you have more queries on SQL!

ON Student_details.Name= Personal_details.Name;
Name Address Roll No. Email ID
Akshay Jaipur 1 [email protected]
Disha Bombay 3 [email protected]
Jitesh Banglore 6 [email protected]

FULL JOIN in SQL:

Full SQL JOIN Query returns all the rows from the left table and the right table. The result set is created by combining all the rows of both tables. The results of LEFT JOIN and RIGHT JOIN are combined to arrive at the result of FULL JOIN.

Syntax:

SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT Student_details.Name, Roll_no, Address
FROM Student_details
FULL OUTER JOIN Personal_details
ON Student_details.Name= Personal_details.Name;
Name Roll No. Address
Akshay 1 Jaipur
Charu 2 NULL
Disha 3 Bombay
Eva 4 NULL
Himanshu 5 NULL
Jitesh 6 Banglore

Learn how to master SQL JOINs for your next interview with our SQL JOIN Interview Questions blog.

Course Schedule

Name Date Details
SQL Training 23 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 30 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 06 Apr 2024(Sat-Sun) Weekend Batch
View Details