April 28, 2024
Headlines

Joins in SQL Server

Craig Johnson07 mins

It’s difficult to imagine how helpful obtaining data from multiple locations would be without joins. Joins are one of the most important topics in SQL Server. Real-world relational databases, such as SQL Server, Oracle, and MySQL, are used to store our data in numerous logical tables linked by a common key value.

hfl-new-banner

As a result, we commonly need to merge data from two or more tables in order to provide the required output based on particular criteria. We can simply acquire this type of data in SQL Server using the SQL JOIN clause.

Types of JOINS in SQL Server
SQL Server mainly supports four types of JOINS

  • INNER JOIN
  • SELF JOIN
  • CROSS JOIN
  • OUTER JOIN

Inner Join
This JOIN returns all data from several tables that meet the required join criteria. It operates as a default join and is the most straightforward and well-liked type of join. We will obtain the same results if we exclude the INNER term from the JOIN quey.

Syntax
The syntax for the INNER JOIN in SQL is:

SELECT columns
FROM table1

INNER JOIN table2
ON table1.column = table2.column;

Self Join
A table is joined to itself utilizing Self JOIN. It implies that each table line is joined with itself and with each and every other table column.

Oneself JOIN can be considered a JOIN of two duplicates of similar tables.

We can do this with the assistance of table name pseudonyms to relegate a particular name to each table’s example.

The table monikers empower us to utilize the table’s impermanent name that we will use in the question.

It’s a useful way to extract hierarchical data and compare rows inside a single table. Best example: To get an Employee and Manager relationship.

Syntax
The syntax for the SELF JOIN in SQL is:

SELECT columns

FROM table1 T1, table1 T2
ON T1.column = T2.column;

CROSS JOIN
In SQL Server, the CROSS JOIN operation allows you to combine multiple tables and retrieve a result set that includes every possible combination of rows from those tables. This operation is also referred to as a “Cartesian join” because it generates the Cartesian product of the linked tables. The Cartesian product represents the combination of all rows from the first table multiplied by all rows from the second table.

The below visual representation illustrates the CROSS JOIN. It will give all the records from table1 and table2 where each row is the combination of rows of both tables.

Syntax
The syntax for the CROSS JOIN in SQL is:

SELECT columns

FROM table1
CROSS JOIN table2

OUTER JOIN
OUTER JOIN in SQL Server returns all records from both tables that satisfy the join condition. In other words, this join will not return only the matching record but also return all unmatched rows from one or both tables.

We can categorize the OUTER JOIN further into three types:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all the records from the left table and matching rows from the right table. It will return NULL when no matching record is found in the right-side table. Since OUTER is an optional keyword, it is also known as LEFT JOIN.

The below visual representation illustrates the LEFT OUTER JOIN.

Syntax
The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns
FROM table1

LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

RIGHT OUTER JOIN

The RIGHT OUTER JOIN retrieves all the records from the right-hand table and matched rows from the left-hand table. It will return NULL when no matching record is found in the left-hand table. Since OUTER is an optional keyword, it is also known as RIGHT JOIN.

The below visual representation illustrates the RIGHT OUTER JOIN.

Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns
FROM table1

RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

 

Related News