The article provides a detailed tutorial on combining data from multiple tables in a database using SQL joins. It starts by introducing the concept of joins and the types of joins available, such as inner join, left join, right join, and full outer join.
The article offers a comprehensive guide to using SQL joins, suitable for beginners and experienced developers.
Table of Contents
- 1 SQL joins
- 2 Types of SQL Joins
- 3 1. SQL INNER JOIN (simple join)
- 4 2. SQL LEFT OUTER JOIN
- 5 3. SQL RIGHT OUTER JOIN
- 6 4. SQL FULL OUTER JOIN
- 7 5. SQL CROSS JOIN
- 8 6. SQL SELF JOIN
- 9 FAQs
- 10 Conclusion
SQL joins are used to combine data from two or more tables in a relational database. They allow you to retrieve data from multiple tables by linking them based on common fields or keys.
This article will explore the different types of SQL joins, including inner, left, right, and full joins, with syntax and examples in SQL Server.
Types of SQL Joins
The following are the various types of SQL Joins:
- SQL LEFT JOIN
- SQL RIGHT JOIN
- SQL INNER JOIN
- SQL FULL JOIN
- SQL CROSS JOIN (Cartesian Product)
- SQL SELF JOIN
The following is a list of SQL joins supported by SQL Server.
|1.||Inner join:||SQL Inner join returns records that have matching values in both tables.|
|2.||Left join:||SQL Left join returns all records from the left table and the records that match from the right table.|
|3.||Right Join:||SQL Right-join returns all records from the right table and those that match from the left table.|
|4.||Full Join:||SQL Full join returns all records if a match is in the left or right table.|
|5.||Cross Join:||A SQL Cross join query returns the number of rows from the left table multiplied by the number of rows from the right table.|
|6.||Self Join:||A Self-join is a join between two copies of the same table with separate alias names.|
1. SQL INNER JOIN (simple join)
The SQL Inner Join, also known as a simple join, returns only the rows with matching values in both tables. It compares the values in the specified columns of each table and returns only the rows where the values match.
Syntax: SQL Inner Join
Inner join has the following syntax:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Visual Illustration (SQL INNER JOIN)
INNER JOIN returns the matching records from table1 and table2 as shown in the diagram below:
It’s important to note that we can also write JOIN instead of INNER JOIN. JOIN and INNER JOIN are the same thing.
To demonstrate SQL joins, let’s create some sample tables.
-- Creating Employee Table (tblEmployee) CREATE TABLE tblEmployee ( Id INT PRIMARY KEY IDENTITY, Name VARCHAR(100) NOT NULL, Gender VARCHAR(10), Salary MONEY, DepartmentId INT ) -- Creating Department Table (tblDepartment) CREATE TABLE tblDepartment ( Id INT PRIMARY KEY, Department VARCHAR(50) NOT NULL, ) -- Insert Records into the Employee table INSERT INTO tblEmployee VALUES('Shekh Ali','Male',50000,1), ('Ayesha','Female',27000,3), ('Rohit','Male',30000,1), ('Johnson','Male',40000,2), ('Anderson','Male',45000,2), ('Sarah','Female',36000,3), ('John','Male',25000,NULL), ('Emma','Female',28000,NULL); -- Insert Records into the Department table INSERT INTO tblDepartment VALUES(1,'IT'), (2,'Payroll'), (3,'HR'), (4,'Marketing'); -- Display records from the both employee and department tables SELECT * FROM tblDepartment; SELECT * FROM tblEmployee
Example: SQL Inner Join
The following example uses the Inner join clause to extract the rows from the
tblEmployee table that have the corresponding rows with the same values in the
SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Department FROM tblEmployee as emp INNER JOIN tblDepartment as dept ON emp.DepartmentId = dept.Id;
The following is the result of the SQL inner join query:
We retrieved six rows out of eight from the table
tblEmployee. However, We did not retrieve the other two records with the NULL value since the Inner Join in SQL Server simply ignores or eliminates non-matching records.
2. SQL LEFT OUTER JOIN
The left outer join returns all rows from the left table and matching rows from the right table. If there is no match in the right table, it returns NULL for the right table’s columns.
Syntax: SQL Left Outer Join
SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;
Visual Illustration (SQL Left Outer Join)
Example: SQL Left Join
SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Department From tblEmployee as emp LEFT JOIN tblDepartment as dept ON emp.DepartmentId = dept.Id;
The image below shows that the LEFT OUTER JOIN returns all records from the left table (tblEmployee) and the matched records from the right table (tblDepartment).
3. SQL RIGHT OUTER JOIN
The SQL right outer join returns all rows from the right table and matching rows from the left table. If there is no match in the left table, it returns NULL for the left table’s columns.
Syntax: SQL Right Join
The right outer join syntax is as follows:
SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;
Visual Illustration (SQL Right Outer Join)
Example: SQL Right Join
SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, dept.Id, dept.Department From tblEmployee as emp RIGHT JOIN tblDepartment as dept on emp.DepartmentId = dept.Id;
As shown in the image below, the Right OUTER JOIN returns all records from the Right table (tblEmployee) and the matched records from the left table (tblDepartment).
4. SQL FULL OUTER JOIN
The Full Outer Join returns all rows from both tables, matching rows from both tables, and NULL values where there is no match.
Syntax: Full Outer Join
The following is the Syntax of the SQL full outer join:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Visual Illustration (SQL Right Outer Join)
Example: Full Right Join
SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Id, dept.Department From tblEmployee as emp FULL JOIN tblDepartment as dept on emp.DepartmentId = dept.Id;
As shown in the image below, the Full Outer Join query returns all rows when there is a match in either the left or right table. If neither table has matching columns, It will fill the missing side with NULL values.
5. SQL CROSS JOIN
A cross join, also known as a Cartesian product, returns all possible combinations of the rows in two or more tables. Unlike the other types of joins, it does not require matching columns.
The cross-join produces a result set where each row from the first table is connected to each row from the second table. The number of rows from the left table multiplied by the number of rows from the right table is returned by a cross-join query.
Syntax: SQL Cross Join (Cartesian Product)
SELECT column_name(s) FROM table1 CROSS JOIN table2;
Visual Illustration (SQL Cross Join)
Example: Cross Join
A SQL Cross Join between the tblEmployee and tblDepartment tables is demonstrated in the following example:
SELECT * FROM tblEmployee CROSS JOIN tblDepartment;
We have eight records in the
tblemployee table and four in the
tbldepartment table. As a result of the cross-join between two tables, 32 records will be produced. The ON clause in a cross-join is not allowed.
6. SQL SELF JOIN
A SQL self-join is a regular join, but the table is joined with itself. In other words, it is a way to combine rows from the same table. It uses separate alias names of the same table.
Syntax: Self Join
The following is the syntax for SQL Self Join:
SELECT t1.column_name(s), t2.column_name(s) FROM table_name t1, table_name t2 WHERE t1.column_name = t2.column_name;
Note: In SQL Server, a Self-join is similar to any other join except that it joins two instances of the same table in the same query.
Q: Why is SQL join needed?
SQL joins help to combine data from two or more tables based on a common field between them. SQL Joins are primarily used to filter records and help to reduce duplicate records in the combined result set from relational database tables.
Q: Differences between Union and Union All in MS SQL Server
In SQL Server, the UNION and UNION ALL combine the results of two or more select queries into a single result set.
The main difference between Union and Union All is that Union extracts only the rows provided in the query, whereas Union All returns all rows from both queries, including duplicates (repeated entries).
Q: Difference between JOIN and UNION in SQL Server
In SQL Server, UNION is used to combine the results of two or more SELECT statements into a single result set, whereas JOIN is used to combine data from multiple tables based on a matching condition.
Q: What Is A Self Join In SQL Server?
In SQL Self Join, the SQL query will join two copies of the same table. That means that each row in a table is joined with itself.
Q: What is a full outer join?
A full outer join in SQL combines the left and right outer join results. Only the records from both tables that match will be included in the final result set. If no matches are found, the results will be displayed as NULL.
Q: What is a SQL join?
A SQL join is a way to combine data from two or more tables in a relational database based on a common column. Joins can help us retrieve more meaningful data by linking related information together.
Q: What are the different types of SQL joins?
There are four main types of SQL joins: inner join, left outer join, right outer join, and full outer join. Each type of join has its specific syntax and use case.
In this article, you learned about different types of SQL Server joins, including INNER JOIN, SELF JOIN, CROSS JOIN, and OUTER JOIN, which can be used to combine data from two or more tables.
- Views In SQL | Types Of Views In SQL Server
- Stored Procedure in SQL Server With Examples
- SQL Delete Cascade – SQL query to delete parent-child referenced rows
- CREATE, ALTER, AND DROP DATABASE IN SQL SERVER
- Primary Key Constraint In SQL Server
- SQL Comparison Operators
- SQL Server Trigger Update, Insert, Delete Examples
I hope you enjoyed reading this article about “SQL JOINS”. If you find it useful, please share it with others. Also, if you have any additional knowledge about the topic covered here, please leave a comment. Thanks
- CTE in SQL Server: Learn Common Table Expressions with Examples - September 14, 2023
- Garbage Collection in C#: Managing Memory Efficiently - September 13, 2023
- Static vs Singleton in C#: Understanding the Key Differences - September 6, 2023