Javatpoint Logo
Javatpoint Logo

SQL Server JOINS

In real life, we store our data in multiple logical tables that are linked together by a common key value in relational databases like SQL Server, Oracle, MySQL, and others. As a result, we constantly need to get data from two or more tables into the desired output based on some conditions. We can quickly achieve this type of data in SQL Server using the SQL JOIN clause. This article gives a complete overview of JOIN and its different types with an example.

The join clause allows us to retrieve data from two or more related tables into a meaningful result set. We can join the table using a SELECT statement and a join condition. It indicates how SQL Server can use data from one table to select rows from another table. In general, tables are related to each other using foreign key constraints.

In a JOIN query, a condition indicates how two tables are related:

  • Choose columns from each table that should be used in the join. A join condition indicates a foreign key from one table and its corresponding key in the other table.
  • Specify the logical operator to compare values from the columns like =, <, or >.

Types of JOINS in SQL Server

SQL Server mainly supports four types of JOINS, and each join type defines how two tables are related in a query. The following are types of join supports in SQL Server:

  1. INNER JOIN
  2. SELF JOIN
  3. CROSS JOIN
  4. OUTER JOIN
SQL Server JOINS

Let us discuss each of these joins in detail.

INNER JOIN

This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and most popular form of join and assumes as a default join. If we omit the INNER keyword with the JOIN query, we will get the same output.

The following visual representation explains how INNER JOIN returns the matching records from table1 and table2:

SQL Server JOINS

INNER JOIN Syntax

The following syntax illustrates the use of INNER JOIN in SQL Server:

INNER JOIN Example

Let us first create two tables "Student" and "Fee" using the following statement:

Next, we will insert some records into these tables using the below statements:

Execute the SELECT statement to verify the records:

Table: Student

SQL Server JOINS

Table: Fee

SQL Server JOINS

We can demonstrate the INNER JOIN using the following command:

This command gives the below result:

SQL Server JOINS

In this example, we have used the admission_no column as a join condition to get the data from both tables. Depending on this table, we can see the information of the students who have paid their fee.

SELF JOIN

A table is joined to itself using the SELF JOIN. It means that each table row is combined with itself and with every other table row. The SELF JOIN can be thought of as a JOIN of two copies of the same tables. We can do this with the help of table name aliases to assign a specific name to each table's instance. The table aliases enable us to use the table's temporary name that we are going to use in the query. It's a useful way to extract hierarchical data and comparing rows inside a single table.

SELF JOIN Syntax

The following expression illustrates the syntax of SELF JOIN in SQL Server. It works the same as the syntax of joining two different tables. Here, we use aliases names for tables because both the table name are the same.

Example

We can demonstrate the SELF JOIN using the following command:

This command gives the below result:

SQL Server JOINS

In this example, we have used the id and city column as a join condition to get the data from both tables.

CROSS JOIN

CROSS JOIN in SQL Server combines all of the possibilities of two or more tables and returns a result that includes every row from all contributing tables. It's also known as CARTESIAN JOIN because it produces the Cartesian product of all linked tables. The Cartesian product represents all rows present in the first table multiplied by all rows present in 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:

SQL Server JOINS

CROSS JOIN Syntax

The following syntax illustrates the use of CROSS JOIN in SQL Server:

Example

We can demonstrate the CROSS JOIN using the following command:

This command gives the below result:

SQL Server JOINS

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 categories 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:

SQL Server JOINS

LEFT OUTER JOIN Syntax

The following syntax illustrates the use of LEFT OUTER JOIN in SQL Server:

Example

We can demonstrate the LEFT OUTER JOIN using the following command:

This command gives the below result:

SQL Server JOINS

This output shows that the unmatched row's values are replaced with NULLs in the respective columns.

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:

SQL Server JOINS

RIGHT OUTER JOIN Syntax

The following syntax illustrates the use of RIGHT OUTER JOIN in SQL Server:

Example

The following example explains how to use the RIGHT OUTER JOIN to get records from both tables:

This command gives the below result:

SQL Server JOINS

In this output, we can see that no column has NULL values because all rows in the Fee table are available in the Student table based on the specified condition.

FULL OUTER JOIN

The FULL OUTER JOIN in SQL Server returns a result that includes all rows from both tables. The columns of the right-hand table return NULL when no matching records are found in the left-hand table. And if no matching records are found in the right-hand table, the left-hand table column returns NULL.

The below visual representation illustrates the FULL OUTER JOIN:

SQL Server JOINS

FULL OUTER JOIN Syntax

The following syntax illustrates the use of FULL OUTER JOIN in SQL Server:

Example

The following example explains how to use the FULL OUTER JOIN to get records from both tables:

This command gives the below result:

SQL Server JOINS

In this output, we can see that the column has NULL values when no matching records are found in the left-hand and right-hand table based on the specified condition.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA