Different Types of SQL Joins

Different Types of SQL Joins

18 Mar 2024
Intermediate
397K Views
10 min read

Different Types of SQL Joins: An Overview

By using joins, as explained in this SQL Server Tutorial, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Based on the two conditions such as by specifying the column from each table to be used for the join. In this SQL Server Course, a typical join condition specifies a foreign key in SQL from one table and its associated key from the other table.

SQL Joins

SQL Joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. Database tables are related to each other with SQL keys. We use this key relationship in SQL Joins. Also, refer to the article SQL Joins with C# LINQ.

Types of SQL Joins

In SQL, there are several types of joins:

  1. Inner Join / Simple Join
  2. Left Outer Join / Left Join
  3. Right Outer Join / Right Join
  4. Full Outer Join
  5. Cross Join
  6. Self Join

1. Inner Join

The inner join in SQL is used to select all rows or columns that match in both tables or as long as the SQL condition is valid.

Inner Join

Syntax

SELECT column1, column2, ...
FROM table1
INNER JOIN table2ON table1.column = table2.column;

Example

Select * from table_1 as t1
inner join table_2 as t2
on t1.IDcol=t2.IDcol

This query joins all columns from table_1 and table_2 where the IDcol column values match, effectively combining related information from both tables.

2. Left Outer Join / Left Join

The LEFT JOIN retrieves all data from the left table (table1) and the rows or columns that match from the right table (table2). If neither table contains any matched rows or columns, it returns NULL.

Left Outer Join / Left Join

Syntax

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example

Select * from table_1 as t1
left outer join table_2 as t2
on t1.IDcol=t2.IDcol

This query combines all columns from table_1 and any matching columns from table_2 based on IDcol, retaining all rows from table_1 even if no match exists in table_2.

3. RIGHT JOIN / RIGHT Outer JOIN

The RIGHT JOIN retrieves all data from the right table (table 2) as well as the matching rows or columns from the left table (table 1). If neither table contains any matched rows or columns, it returns NULL.

RIGHT JOIN / RIGHT Outer JOIN

Syntax

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example

Select * from table_1 as t1
right outer join table_2 as t2
on t1.IDcol=t2.IDcol

This query merges all columns from table_1 & table_2 where IDcol values match, ensuring that all rows from table_2 are included even if no matches exist in table_1.

4. Full Outer Join

It is a result set that combines both LEFT JOIN & RIGHT JOIN. The connected tables return all records from both tables and place NULL if no matches are found in the table. It is also known as a FULL OUTER JOIN.

Full Outer Join

Syntax

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Example

Select * from table_1 as t1
full outer join table_2 as t2
on t1.IDcol=t2.IDcol

This query includes all rows and columns from table_1 and table_2, matching them based on IDcol wherever feasible and filling in missed matches with null values to guarantee that everything is included.

5. CROSS JOIN

CARTESIAN JOIN, which returns the Cartesian product of two or more connected tables, is another name for it. The CROSS JOIN creates a table that merges each row from the first table with each row from the second table. There is no need to provide any conditions in CROSS JOIN.

Syntax

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

6. SELF JOIN

It is a SELF JOIN that was used to build a table by combining two tables. It names at least one table temporarily in an SQL statement.

Syntax

SELECT column1, column2, ...
FROM table1 AS alias1
INNER JOIN table1 AS alias2
ON alias1.column = alias2.column;

Difference between the types of SQL joins in SQL Server

Join TypeDescriptionResult SetUse Case
Inner Join(Default) Returns records with matching values in both tables.Only matched rowsMost common join, retrieving related data from two tables.
LEFT OUTER JOINReturns all records from the left table and matching records from the right table. For unmatched rows in the right table, null values are filled for columns from the right table.All rows from the left table matched records from the right tablePreserving all data from the left table and including matching data from the right table.
RIGHT OUTER JOINReturns all records from the right table and matching records from the left table. For unmatched rows in the left table, null values are filled for columns from the left table.All rows from the right table matched records from the left tablePreserving all data from the right table and including matching data from the left table.
FULL OUTER JOINReturns all records from both tables, filling null values for unmatched columns.All rows from both tablesIncluding all data from both tables regardless of matches.
SELF JOINJoins a table to itself based on a matching condition between two columns within the same table.Matches rows within a single tableUsed for hierarchical data or finding relationships within a single table.
CROSS JOINCartesian product - returns all possible combinations of rows from the joined tables. It can result in a large dataset.All possible combinations of rows from both tablesLess common, typically used for generating all possible combinations of data.

Best Practices for Using SQL Joins

  • Before selecting the right join type, be sure you understand the links between the tables and the data model.
  • To get matching records from both tables, use an INNER JOIN.
  • To get every record from the left table and every matching record from the right table, use an LEFT JOIN.
  • To get every record from the right table and every matching record from the left table, use RIGHT JOIN.
  • If at all possible, avoid using CROSS JOIN since it produces a Cartesian product.
  • To make tables easier to read and prevent ambiguity, use aliases.
  • For large datasets, avoid joining on non-indexed columns to maximize efficiency.
  • To maximize performance, evaluate execution plans and test queries.
  • For optimal speed, consider utilizing EXISTS or IN clauses in place of joins in some instances.

Read More:

Summary

SQL Joins are a powerful technique to integrate data from many databases, with choices such as Inner, Left, Right, Full Outer, Cross, and Self Joins allowing you to retrieve certain data sets based on table relationships.

FAQs

Q1. What are the 4 types of joins in SQL?

In SQL, there are four types of JOINs: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN. However, keep in mind that OUTER JOINS are divided into two types: LEFT OUTER JOIN and RIGHT OUTER JOIN.

Q2. What are the 5 different types of table joins?

There are five types of join operations: inner, left, right, full, and cross joins.

Q3. Can you join 3 tables in SQL?

Like most people, you probably learned SQL JOINs by joining two tables. You've probably wondered if it's possible to join three or more tables in SQL using the JOIN keyword. The quick answer is, yes, it is possible!

Q4. How to join 10 tables in SQL?

Joining 10 tables in SQL with no duplicates is a difficult operation, but it is possible with the [code]UNION[/code] operator. You can begin by joining the first two tables, then use the result to join the third table, and so on until all ten tables have been joined.

Q5. Which SQL join is the best?

Using an inner SQL join rather than an outer join

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this