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
Table of Contents
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
- Left table. (Left Join)
- Right table. (Right Join)
- 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.
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
1 2 3 4 5 6 7 8 9 10 11 12 | create table Customers ( CustomerID int primary key, Name varchar(100) ); insert into Customers values(1, 'Kevin Costner'); insert into Customers values(2, 'Akshay Kumar'); insert into Customers values(3, 'Sean Connery'); insert into Customers values(4, 'Sanjay Dutt'); insert into Customers values(5, 'Sharukh Khan'); |
Tables
The Tables contain the TableNo & customerID of the customer who has booked the table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table Tables ( TableNo int primary key, CustomerID int null, CONSTRAINT FK_Tables_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ); insert into Tables values(1, null); insert into Tables values(2, 1); insert into Tables values(3, 2); insert into Tables values(4, 5); insert into Tables values(5, null); |
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.
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
1 2 3 4 5 6 | SELECT <Columns> FROM tableA JOIN tableB ON (join_condition) |
Example
1 2 3 4 5 6 | Select C.CustomerID,C.Name, T.TableNo From Customers C join Tables T on (C.CustomerID=T.CustomerID) |
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
1 2 3 4 5 6 | SELECT <Columns> FROM tableA LEFT JOIN tableB ON (join_condition) |
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
The part in blue above shows the data rows returned by the left join. As you can see, the result includes
- Matching rows from Table B
- Unmatched rows from Table A with null for columns from Table B
Query
1 2 3 4 5 6 | Select C.CustomerID,C.Name, T.TableNo From Customers C Left join Tables T on (C.CustomerID=T.CustomerID) |
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.
Syntax
1 2 3 4 5 6 | SELECT <Columns> FROM tableA RIGHT JOIN tableB ON (join_condition) |
The part in blue above shows the data rows returned by the Right join. As you can see the result includes
- Matching rows from Table A
- Unmatched rows from Table B with null for columns from Table A
Query
1 2 3 4 5 6 | Select T.TableNo, C.CustomerID,C.Name From Customers C Right join Tables T on (C.CustomerID=T.CustomerID) |
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.
1 2 3 4 5 6 | Select T.TableNo, C.CustomerID,C.Name From Tables T Right join Customers C on (C.CustomerID=T.CustomerID) |
Full Join
In Full Join, the result includes all the rows from both the table as shown in the following diagram.
The part in blue above shows the data rows returned by the Right join. As you can see the result includes
- Matching rows from Table A & Table B
- Unmatched rows from Table A with null for columns from Table B
- Unmatched rows from Table B with null for columns from Table A
Syntax
1 2 3 4 5 6 | SELECT <Columns> FROM tableA FULL JOIN tableB ON (join_condition) |
Query
1 2 3 4 5 6 | Select C.CustomerID,C.Name, T.TableNo From Customers C Full join Tables T on (C.CustomerID=T.CustomerID) |
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
1 2 3 4 5 | SELECT <Columns> FROM tableA CROSS JOIN tableB |
Query
1 2 3 4 5 | Select C.CustomerID,C.Name, T.TableNo From Customers C cross join Tables T |
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
1 2 3 4 5 6 | SELECT <Columns> FROM tableA A1 JOIN tableA A2 ON (A1.Column1=A2.Column2) |
References
Summary
In this article, we learned about the SQL join types in SQL Server
