Let’s set up sample tables for demonstration.
Setting up sample tables
First, create a new schema named
hr
:
Third, insert some rows into the
candidates
and employees
tables:
Let’s call the
candidates
table the left table and the employees
table the right table.SQL Server Inner Join
Inner join produces a data set that includes rows from the left table which have matching rows from the right table.
The following example uses the inner join clause to get the rows from the
candidates
table that have the corresponding rows with the same values in the fullname
column of the employees
table:
Here is the output:

The following Venn diagram illustrates the result of the inner join of two result sets:
SQL Server Left Join
Left join selects data starting from the left table and matching rows in the right table. The left join returns all rows from the left table and the matching rows from the right table. If a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls.
The left join is also known as left outer join. The outer keyword is optional.
The following statement joins the
candidates
table with the employees
table using left join:
Here is the output:

The following Venn diagram illustrates the result of the left join of two result sets:

To get the rows that available only in the left table but not in the right table, you add a
WHERE
clause to the above query:
The following picture shows the output:

And the following Venn diagram illustrates the result of the left join that selects rows available only in the left table:

SQL Server Right Join
The right join or right outer join selects data starting from the right table. It is a reversed version of the left join.
The right join returns a result set that contains all rows from the right table and the matching rows in the left table. If a row in the right table that does not have a matching row in the left table, all columns in the left table will contain nulls.
The following example uses the right join to query rows from
candidates
and employees
tables:
Here is the output:

Notice that all rows from the right table (
employees
) are included in the result set.
And the Venn diagram that illustrates the right join of two result sets:
Similarly, you can get rows that are available only in the right table by adding a
WHERE
clause to the above query as follows:
Here is the output:

And Venn diagram that illustrates the operation:
SQL Server full join
The full outer join or full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have NULL values.
The following example shows how to perform a full join between the
candidates
and employees
tables:
Here is the output:

The Venn diagram that illustrates the full outer join:
To select rows that exist either left or right table, you exclude rows that are common to both tables by adding a
WHERE
clause as shown in the following query:
Here is the output:

And the Venn diagram that illustrates the above operation:
In this tutorial, you have learned various kinds of SQL Server joins that combine data from two tables.
0 Comments