Join in SQL Server (Concept and Examples for each join category)
Join is an important function for TSQL enabling us to retrieve data from two or more tables based on logical relationships between the tables. Joins are performed by specifying the column from each table to be used for the join and by specifying logical operator defining how the data will be picked up from the table.
Joins are categorized as below:
Inner joins - Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. This is most natural way of joining.
Outer joins – This is classified as there sub-categories as below:
LEFT JOIN or LEFT OUTER JOIN: The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN: The result set of a right outer join includes all the rows from the right table specified in the RIGHT OUTER clause, not just the ones in which the joined columns match. When a row in the right table has no matching rows in the left table, the associated result set row contains null values for all select list columns coming from the left table.
FULL
Cross joins - Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.
Now let us review below detailed examples to understand joins better with the TSQL Syntax:
First let us consider a situation of a fictional “sales” database where we are dealing with three related tables as below:
Base Data in two tables are as below:
Now we will use various types of joins to retrieve data from these three tables of sales database.
Inner Join Example:
Let us join our two sales tables using Inner Join using below code:
USE Sales;
GO
SELECT *
FROM dbo.tbl_EmpMaster as e
INNER JOIN dbo.tbl_SalesMaster as s
ON e.EID = s.EID
ORDER BY e.EID
Result:
This result returns all columns from both sales tables by matching the EID. (This means below example will return all columns only if Employee logged at least 1 sale. Here we will not see Kathy as she did not sell anything yet)
However this was a simple example of Inner join. Any join can accept other logical expressions and return specific columns. For example, below example will return EID, Name and Email ID of only those employees who logged a sales amount >200 for at least one time.
USE Sales;
GO
SELECT DISTINCT e.EID, e.Name, e.EMail
FROM dbo.tbl_EmpMaster as e
INNER JOIN dbo.tbl_SalesMaster as s
ON e.EID = s.EID
where s.Sales_Amnt > 200
ORDER BY e.EID
Result:
In this case, Bill is only returned as he only sold Banana for $2345.
Left Outer Join:
We are now joining our two sales tables using Left outer join as below:
USE Sales;
GO
SELECT *
FROM dbo.tbl_EmpMaster as e
LEFT JOIN dbo.tbl_SalesMaster as s
ON e.EID = s.EID
ORDER BY e.EID
Result:
So in this result set, we can see every data from left table (tbl_EmpMaster) is returned and if the right table does not have matching row, it reported NULL. (Kathy did not sell anything but still her name appears here).
Right Outer Join:
Now let us join both employee tables using a right outer join as below:
USE Sales;
GO
SELECT *
FROM dbo.tbl_EmpMaster as e
RIGHT JOIN tbl_AllEmp as a
ON e.EID = a.EID
ORDER BY a.EID
Result:
In this case, all data from right table (tbl_AllEmp) is returned and if left table has no matching row, it is reported as NULL. (We can see EID 2 from TECH Department is appearing here).
Full Outer Join:
To understand it better, let us change our data a little. We will added a new Employee in tbl_EmpMaster but will not add it in tbl_AllEmp. New employee is named as “Temp” and assigned a EID as 6.
So now our base data looks as below:
Now let us execute below code to perform a Full Join:
USE Sales;
GO
SELECT *
FROM dbo.tbl_EmpMaster as e
full JOIN tbl_AllEmp as a
ON e.EID = a.EID
ORDER BY a.EID
Result:
This result shows that all the rows from both tables are returned and if there is no matching row, data in other hand’s table is reported as NULL. (Here we can see both EID 6 from tbl_EmpMaster and EID 2 from tbl_AllEmp).
Cross Join:
Now let us cross join tbl_AllEmp and tbl_EmpMaster using below oode:
USE Sales;
GO
SELECT *
FROM tbl_AllEmp e
CROSS JOIN dbo.tbl_EmpMaster as a
ORDER BY e.EID
Result:
This result return all rows from the left table and each row from the left table is combined with all rows from the right table. (Hence there are 5*5=25 rows)
Comments