Please enable Javascript to correctly display the contents on Dot Net Tricks!
 
Ready to master latest .NET, JavaScript framework and Mobile development skills?
Join our training programs and take your career to the next level!

Different Types of SQL Joins

Posted By : Shailendra Chauhan, 02 Mar 2011
Updated On : 24 Jun 2014
Total Views : 119,509   
Version Support : SQL Server 2005,2008,2012
 
Keywords : Types of Sql Join pdf, Introduction to Sql Join, Inner Join, Outer Join, Left Outer Join, Right Outer Join, Full Outer Join, Cross Join, Self Join

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 data tables are related to each other with keys. We use these keys relationship in sql joins. Also, refer the article SQL Joins with C# LINQ.

Types of Joins

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

  1. Inner Join

    Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

     Select * from table_1 as t1
    inner join table_2 as t2
    on t1.IDcol=t2.IDcol 

  2. Outer Join

    We have three types of Outer Join.

    1. Left Outer Join

      Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. 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

      Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. 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 the tables.If there are no columns matching in the both tables, it returns NULL values. 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 

  3. Cross Join

    Cross join is a cartesian join means 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 number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

     Select * from table_1
    cross join table_2 

  4. 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 following three tables and data in these three tables is shown in figure. You can download the SQL script used in this article by using 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 following two tables and data in these two tables is shown in 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?

I hope you will enjoy these valuable tricks while query the data from database 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.

 
Recommended Articles!
 
About the Author
Hey! I'm Shailendra Chauhan author, developer with more than 5 years of hand over Microsoft .NET technologies. I am a .NET Consultant, founder & chief editor of www.dotnet-tricks.com and www.webgeekschool.com. I am author of books ASP.NET MVC Interview Questions and Answers & LINQ Interview Questions and Answers.
I love to work with web applications and mobile apps using Microsoft technology including ASP.NET, MVC, C#, SQL Server, WCF, Web API, Entity Framework,Cloud Computing, Windows Azure, jQuery, jQuery Mobile, Knockout.js, Angular.js and many more web technologies. More...
 
Free .NET Interview Books
 
Browse By Category
Learn In Hindi
 
 
Like us on Facebook