Joins in SQL - SQL Joins

What is Joins in SQL?

SQL Joins is used to combine rows of two or more tables by using common values.

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

Example:

Let us consider two tables first is Student details

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

Another table is Personal Detail.

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

Let us see how to join the two tables in SQL

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

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

 

Types of Joins in SQL

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 Joins in SQL:

Inner SQL Join Query returns a value when there is a match in both the tables.

Watch this Inner Join in SQL video

Left Joins in SQL:

Left SQL Join Query returns all the values of the left table and matched the rows from the right table.

Watch this Left Join in SQL video

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;

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]

Right Joins in SQL:

Right SQL Join Query returns all the values of the right table and matched the rows from the left table.

Watch this Right Join in SQL video

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 Joins in SQL:

Full SQL Join Query returns all the rows from the left table and the right table.

Watch this Full Join in SQL video

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

Wish to get certified in SQL! Learn SQL from top SQL experts and excel in your career with intellipaat’s SQL certification.