SQL join types

 Joins are one of the basic constructions of SQL and Databases as such - they combine records from two or more database tables into one row source, one set of rows with the same columns. And these columns can originate from either of the joined tables as well as be formed using expressions and built-in or user-defined functions. Depending on join type and join restrictions returned row count can be from 0 till all possible combinations of involved tables. So if one has two tables containing 10 000 rows each then the maximum number of resultant rows can be 10 000 * 10 000 = 100 000 000 rows. Databases are built to make joins as efficient as possible. It means almost always joining data in database is more efficient than doing that somewhere else. It also means one has to know the power and possibilities of joins to fully exploit their strength. And it doesn't matter whether one is using Oracle, SQL Server, MySQL, IBM DB2, PostgreSQL or whatever else DBMS.

Types of Joins

• Inner join
• Right Outer join
• Left Outer join
• Full Outer join
• Self join
• Cross join

 Depending on condition existence

Depending on whether we add any join condition or not there are following join types (yellow in model):

CROSS JOIN (synonyms also CARTESIAN JOIN, CARTESIAN PRODUCT) - there isn't any join condition or it is always true. All other join types degrade to CROSS JOINS as soon as join condition (-s) is (are) always true.

JOIN WITH RESTRICTION - there is applied join condition to joined tables. One can write join with restriction using different syntactic notations. As already said above every join with restriction may degrade to cross join.

NATURAL JOIN - syntactic notation joining source tables on all columns having the same name. This can be quite dangerous as explained below in the chapter for Natural join. Natural joins always are Equi joins.

QUALIFIED JOIN - user has possibility to define which columns are used in join condition.

NAMED COLUMNS JOIN - syntactic notation joining source tables on user defined columns having the same name. This is less dangerous than Natural join and just short form of writing Equi joins on some common columns joined together. Named columns joins always are Equi joins.

CONDITIONAL JOIN - fully controllable syntax by user. This is the most widespread and most useful syntactic convention. Depending on used predicates in join condition it may be Equi join as well as Non-equi join.

 Depending on row selection

Depending on whether only rows satisfying join condition are selected or all rows are selected in one or both involved tables, joins are divided into (green in model):

INNER JOIN - only rows satisfying selection criteria from both joined tables are selected.

LEFT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.

RIGHT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.

FULL OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.

 Depending on comparison operator

Depending on used comparison operator in join condition there are following join types (blue in model):

PREDICATE OPERATOR TYPE - based on predicate operator type (i.e. equality and everything other) joins are divided into two parts Equi joins and Nonequi joins.

EQUI JOIN - join condition uses only equality predicate "=". It can be both explicit for Conditional join and implicit for Natural join and Named columns join.

THETA (NONEQUI) JOIN - everything other than equality predicate "=", for example ">=", between. This can be used only by Conditional joins.

 Depending on used tables

There is one special case for classification based on what tables are involved in join (orange in model).

SELF JOIN - table is joined to itself.

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 

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 

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 

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
 

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