Types of Join
By : Kasim Wirama, MCDBA
In this article, I would like to introduce types of join in SQL Server. I give examples inner join, cross join, left outer join, full outer join and left semi join and left anti semi join.
Before I progress to each joins, here is the tables script as example :
CREATE TABLE customer
(custid CHAR(3) NOT NULL PRIMARY KEY,
firstname VARCHAR(10) NOT NULL);
GO
CREATE TABLE sales
(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty INT);
GO
INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);
Inner join
Inner join will join 2 input sets and output them if both of them match the join criteria. To display customer and sales related to the customer, here is the query and output
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
INNER JOIN sales AS s
ON c.custid = s.custid
custid firstname salesID custid qty
------ ---------- ----------- ------ -----------
ant anton 1 ant 10
rng rangga 2 rng 20
rng rangga 3 rng 25
(3 row(s) affected)
Cross join
Cross join will join 2 input sets and each rows in both 2 inputs will have combination for each other. Most business requirement doesn’t use this join frequently. Cross join doesn’t need join predicate. The example and result for this join are as below :
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
CROSS JOIN sales AS s
custid firstname salesID custid qty
------ ---------- ----------- ------ -----------
ant anton 1 ant 10
ant anton 2 rng 20
ant anton 3 rng 25
ant anton 4 smi 10
joh johan 1 ant 10
joh johan 2 rng 20
joh johan 3 rng 25
joh johan 4 smi 10
rng rangga 1 ant 10
rng rangga 2 rng 20
rng rangga 3 rng 25
rng rangga 4 smi 10
(12 row(s) affected)
Notice that number of rows in cross join is the multiplication of number of rows in customer and sales table.
Left outer join
Left outer join will output all rows from left input sets based on specified join predicate, even though rows from left input sets doesn’t necessarily have its match at right input sets. For example : display all customers with existing and non existing sales.
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
LEFT OUTER JOIN sales AS s
ON c.custid = s.custid
custid firstname salesID custid qty
------ ---------- ----------- ------ -----------
ant anton 1 ant 10
joh johan NULL NULL NULL
rng rangga 2 rng 20
rng rangga 3 rng 25
(4 row(s) affected)
Notice that custid Joh doesn’t have sales. A LEFT OUTER JOIN B will be same as B RIGHT OUTER JOIN A.
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM sales AS s
RIGHT OUTER JOIN customer AS c
ON c.custid = s.custid
Full outer join
This join will join both input sets, and will output each rows of both of them even though they don’t have pairs for each other. Let’s say, I would like to display all customer and all sales information, with the following example :
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
FULL OUTER JOIN sales AS s
ON c.custid = s.custid
custid firstname salesID custid qty
------ ---------- ----------- ------ -----------
ant anton 1 ant 10
joh johan NULL NULL NULL
rng rangga 2 rng 20
rng rangga 3 rng 25
NULL NULL 4 smi 10
(5 row(s) affected)
Notice that custid joh doesn’t have sales, and salesid 4 doesn’t have corresponding customer information in customer table.
Left Semi join
Left semi join will output outer input set which exists in inner input set and remove duplication of resultset, it is look like inner join. SQL Server doesn’t have command for semi join, the semi join is introduced in EXISTS or IN keyword.
Illustration for semi join is to display customer information that has sales.
SELECT c.custid, c.firstname
FROM customer AS c
WHERE EXISTS
(
SELECT * FROM sales AS s
WHERE s.custid = c.custid
);
custid firstname
------ ----------
ant anton
rng rangga
(2 row(s) affected)
Or using IN keyword :
SELECT c.custid, c.firstname
FROM customer AS c
WHERE custid IN
(
SELECT custid FROM sales AS s
WHERE s.custid = c.custid
)
Left anti semi join
Left anti semi join will output outer input set which doesn’t exists in inner input set and remove duplication of resultset. SQL Server doesn’t have command for anti semi join, the anti semi join is introduced in NOT EXISTS or NOT IN keyword.
Illustration for anti semi join is to display customer information that doesn’t have corresponding sales.
SELECT c.custid, c.firstname
FROM customer AS c
WHERE NOT EXISTS
(
SELECT * FROM sales AS s
WHERE s.custid = c.custid
);
custid firstname
------ ----------
joh johan
(1 row(s) affected)
Using NOT IN will be:
SELECT c.custid, c.firstname
FROM customer AS c
WHERE custid NOT IN
(
SELECT custid FROM sales AS s
WHERE s.custid = c.custid
)