Different Types of SQL Joins

Print 9 min read

31 Aug 2022

Intermediate

387K Views

By using joins, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Based on the two conditions such as by specifying the column from each table to be used for the join. And a typical join condition specifies a foreign key from one table and its associated key from the other table.

Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

SQL Joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. Basically, database tables are related to each other with keys. We use this keys relationship in SQL Joins.

Also, refer to the article SQL Joins with C# LINQ.

Types of SQL Joins

In SQL Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

Inner Join
Inner join returns only those records/rows that match/exist in both the tables. The inner join generally depends upon FORM or the WHERE clause in which the data of the first table is joined using another table using the terms 'inner join' followed by the second table to be joined with the first table.
The syntax for Inner Join is as
```
 Select * from table_1 as t1
inner join table_2 as t2
on t1.IDcol=t2.IDcol 
```
Outer Join
Outer join is also called Right join and the primary reason a right join would be used is when we are joining more than two tables from the database. In these use-cases, using a right join method is preferable because it can avoid restructuring our whole query to join one table. Outside of this thing, the right joins are used very rarely due to their complexity, so for such simple joins, it’s better to use a left join than a right as it will be easier for our query to be read and understood by others while developing a DBMS query.
We have three types of Outer Join.
1. Left Outer Join
  Left outer join returns all records/rows from the left table and from the right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. The syntax for Left outer Join is as :
```
 Select * from table_1 as t1
left outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
2. Right Outer Join
  A right outer join returns all records/rows from the right table and from the left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. The syntax for right outer Join is as :
```
 Select * from table_1 as t1
right outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
3. Full Outer Join
  Full outer join combines left outer join and right outer join. This join returns all records/rows from both tables. If there are no columns matching in both tables, it returns NULL values. The syntax for full outer Join is as :
```
 Select * from table_1 as t1
full outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
Cross Join
Cross join is a cartesian join means the cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record numbers from both the tables means each row on the left table will be related to each row of the right table. The syntax for right outer Join is as :
```
 Select * from table_1
cross join table_2 
```
Self Join
Self-join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically, we have only three types of joins: Inner join, Outer join, and Cross join. We use any of these three JOINS to join a table to itself. Hence Self-join is not a type of SQL join.

Join Examples

Suppose we follow three tables and the data in these three tables is shown in the figure. You can download the SQL script used in this article by using the link.

Inner Join

 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
ORDER BY t1.OrderID

Inner Join among more than two tables

 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID
ORDER BY t1.OrderID

Inner Join on multiple conditions

 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID AND t1.ContactNo = t2.ContactNo
ORDER BY t1.OrderID

Left Outer Join

 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
LEFT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Right Outer Join

 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
RIGHT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Full Outer Join

 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
FULL OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Cross Join

 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price
FROM tblProduct AS t0, tblOrder AS t1
ORDER BY t0.ProductID

Self Join

To understand Self Join, suppose we follow two tables, and the data in these two tables is shown in the figure.

 CREATE TABLE emp 
( 
id int NOT NULL primary key, 
name varchar(100) NULL, 
designation varchar(50) NULL, 
supid int foreign key references emp(id) ) -- In this table we have a Foreign key supid that references its own Primary Key id. We use it for Self Join 
INSERT INTO emp(id,name,designation) VALUES(1,'mohan','Manger') 
INSERT INTO emp(id,name,designation,supid) VALUES(2,'raj kumar','SE',1) 
 INSERT INTO emp(id,name,designation) VALUES(3,'bipul kumar','Manager') 
 INSERT INTO emp(id,name,designation,supid) VALUES(4,'mrinal kumar','SE',2) 
 INSERT INTO emp(id,name,designation,supid) VALUES(5,'jitendra kumar','SE',2)

 CREATE TABLE empinfo 
( 
id int primary key, 
 address varchar(50) NULL 
) 
INSERT INTO empinfo(id,address) VALUES(1,'Delhi') 
INSERT INTO empinfo(id,address) VALUES(2,'Noida') 
INSERT INTO empinfo(id,address) VALUES(4,'Gurgaon') 
INSERT INTO empinfo(id,address) VALUES(6,'Delhi') 
INSERT INTO empinfo(id,address) VALUES(7,'Noida')

 select e.id,e.name,e.supid as managerid, ei.name as managername from emp e left join emp ei on e.supid=ei.id; 
-- outer keyword is optional

What do you think?

SQL join statements help us to combine the rows and columns and parts of various tables of the database and represent them as a single table. With the use of joins in SQL, we can easily combine and present data into a single table. Different types of join make data accessibility easy for us to perform the queries and transactions on the data required for the application. We can perform joins based on one or more common fields in the two or more tables accordingly as discussed in this brief article.

I hope you will enjoy these valuable tricks while querying the data from databases like SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.