SQL Join Types in SQL Server

In this tutorial, we will provide an overview of the various SQL Join types with examples. We also explore the similarities and differences between them. The SQL Joins gives us an ability two join data from two are more tables. We learned the basics of SQL join

Join types

There are three different join types are available. They are inner, outer & cross join. The outer join is further divided into left, right & full.

The Tables are joined using a join condition (except cross join where there is no join condition). If the rows of both the tables satisfy the join condition, they are included in the result. The join types differ on how they treat the left out rows or unmatched rows.

The inner join does no include the unmatched rows in the final result, but only the rows that match.

The outer joins include rows that match. But it also includes the unmatched rows from

  1. Left table. (Left Join)
  2. Right table. (Right Join)
  3. Both tables (Full join)

Cross join does not have a join condition. It joins all the rows from the left table to all the rows from the right table creating the Cartesian product.

Join types in SQL Server

Sample Database

Consider the following tables from the table reservation system of a restaurant It has two tables. customers and tables.

Customers

The customer table has two columns. CustomerID ( primary key ) & Name

Customers Master

Tables

The Tables contain the TableNo & customerID of the customer who has booked the table

Restaurant Tables Master

Inner Join

Inner join includes only those records in the result that satisfies the join condition. That is they return only those rows that exist in both the tables.

Inner join is the default in SQL Server.

The following Venn diagram explains the inner join very accurately.

Join Type : Inner Join
Inner Join Type

In the above Venn diagram, the circles represent the two tables. Table A and Table B, which we would like to join using the inner join.

The intersection part in blue above shows the data rows which satisfy the join condition. The inner join of the above tables returns the matching rows in both these tables.

Syntax

Example

Inner Join Type Example

As you can see from the above result, there are only three rows, that satisfy the join condition. All the other rows are left out from the final result.

Left Join

Left Join is one of the most commonly used join type.

In Left join, the result includes all the rows from the left table along with the matching rows from the right table. The LEFT Table is the one which comes at the left side of the LEFT JOIN statement

Syntax

The following is the syntax of the Left Join

The tableA which comes first or left of the LEFT JOIN clause is the Left table. TableB which comes after (or Right of the) LEFT JOIN the clause is the Right table.

The following Venn diagram explains the Left Join

Join Type : Left Join

The part in blue above shows the data rows returned by the left join. As you can see, the result includes

  1. Matching rows from Table B
  2. Unmatched rows from Table A with null for columns from Table B

Query

As you can see, the result includes all customers. The customer with id 3 & 4 does not have any matching rows from Tables. Hence the value of TableNo column is NULL.

Right Join

Right Join is the mirror image of the Left Join.

In RIght Join, the result includes all the rows from the right table along with the matching rows from the left table.

Join Type : Right Join

Syntax

The part in blue above shows the data rows returned by the Right join. As you can see the result includes

  1. Matching rows from Table A
  2. Unmatched rows from Table B with null for columns from Table A

Query

As you can see, the result includes all tables. The tables with no 1 & 5 do not have any matching rows from the Customers table. Hence the value of the CustomerID & Name column is NULL.

You exchange the position of tables as shown here. The result of the following query is the same as the LEFT join above.

Full Join

In Full Join, the result includes all the rows from both the table as shown in the following diagram.

Join Type : Full Join

The part in blue above shows the data rows returned by the Right join. As you can see the result includes

  1. Matching rows from Table A & Table B
  2. Unmatched rows from Table A with null for columns from Table B
  3. Unmatched rows from Table B with null for columns from Table A

Syntax

Query

As you can see, the result includes all tables & customers. A Null value is

Cross Join

Cross join joins every row of table A with every row of table B. You cannot specify a join condition with a cross join. Hence if table A has 5 rows and table B has 5 rows, then cross join will result in 5X5 = 25 rows

Syntax

Query

As you see above, each customer is joined with every row from the tables.

Self Join

Self-join is not a join type. Self-join is when you join a table with itself. That is, both the tables in the join operations are the same

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

In this article, we learned about the SQL join types in SQL Server

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top