Look no further! In this blog, we'll explore the most common types of SQL joins, including Inner Join, Left Join, Right Join, and Full Outer Join, and provide clear explanations and examples of how to use each one. Whether you're a beginner or an experienced SQL user, understanding these join types is crucial for efficient and effective database management. So, let's dive into the world of SQL joins and enhance your SQL skills!
In SQL, joins are used to combine data from two or more tables based on a related column between them. There are different types of SQL joins that you can use to retrieve data from multiple tables.
Types of joins in SQL:
1.Inner Join
2.Left Join
3.Right Join
4.Full Outer Join
 |
| Types Of SQL Joins |
To demonstrate these join types, let's consider two tables: Employee and Department. The Employee table contains employee data, and the Department table contains department data. Both tables are related to each other through the DepartmentID field.
 |
| Employee Table |
 |
| Department Table |
Now that we have our tables and sample data set up, let's dive into each type of join in SQL.
1.INNER JOIN-
Inner Join is the most common type of join in SQL. It returns only the rows from both tables that have matching values in the join condition.
Here's the syntax for Inner Join:
SELECT column1, column2, ...FROM table1
INNER JOIN table2
ON table1.column = table2.column
Here's an example of Inner Join using our Employee and Department tables:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentID = d.DepartmentID;
4.FULL OUTER JOIN-
FULL OUTER JOIN returns all the rows from both tables. If there are no matching rows in either table, NULL values will be returned. The syntax for FULL OUTER JOIN is as follows:
Here's the syntax for Full Outer Join:
SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Using the provided tables and sample data, let's find all the employees and their corresponding departments using FULL OUTER JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentID = d.DepartmentID;
This query will return the following result:
 |
| Inner Join Output |
2.LEFT JOIN-
LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there are no matching rows in the right table, NULL values will be returned. The syntax for LEFT JOIN is as follows:Here's the syntax for Left Join:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Using the provided tables and sample data, let's find all the employees and their corresponding departments, even if they don't belong to any department using LEFT JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
LEFT JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
This query will return the following result:
3.RIGHT JOIN-
RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there are no matching rows in the left table, NULL values will be returned. The syntax for RIGHT JOIN is as follows:
Here's the syntax for Right Join:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
JOIN table1.column = table2.column;
Using the provided tables and sample data, let's find all the departments and their corresponding managers using RIGHT JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
RIGHT JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
This query will return the following result:
LEFT JOIN table2
ON table1.column = table2.column;
Using the provided tables and sample data, let's find all the employees and their corresponding departments, even if they don't belong to any department using LEFT JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
LEFT JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
This query will return the following result:
 |
| Left Join Output |
RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there are no matching rows in the left table, NULL values will be returned. The syntax for RIGHT JOIN is as follows:
Here's the syntax for Right Join:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
JOIN table1.column = table2.column;
Using the provided tables and sample data, let's find all the departments and their corresponding managers using RIGHT JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
RIGHT JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
This query will return the following result:
 |
| Right Join Output |
FULL OUTER JOIN returns all the rows from both tables. If there are no matching rows in either table, NULL values will be returned. The syntax for FULL OUTER JOIN is as follows:
Here's the syntax for Full Outer Join:
SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Using the provided tables and sample data, let's find all the employees and their corresponding departments using FULL OUTER JOIN:
SELECT Employee.FirstName, Employee.LastName, Department.DepartmentName
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
This query will return the following result:
 |
| Full Outer Join Output |
It is important to note that the choice of join type depends on the data you are working with and the specific requirements of your query. Each join type has its strengths and weaknesses, and it is crucial to choose the right one to get the desired result.
0 Comments