Different Types of SQL Joins

What is 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.

Read More - DBMS Interview Questions For Freshers

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.

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 in SQL 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.

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.

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.

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

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:

• Top SQL Server Interview Questions and Answers

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. Also, enjoy our SQL Server Course for a better understanding of SQL concepts.