Sql servee Joins

SQL Server Join Examples

joining tables to obtain the needed data for a query, script or stored stored procedure is a key concept as you learn about SQL Server development.

Before we jump into code, let's provide some baseline information on the joins options in SQL Server:

• INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data.  Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.




• Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries.  Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application.  As such, please take the time to understand the data being requested then select the proper join option.
• Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.




• LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table.  On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.




• Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another.  So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.




• RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table.  On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.
• Self -Join - In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.
• CROSS JOIN - Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows.  The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table.  Please heed caution when using a CROSS JOIN.
• FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

INNER JOIN Example
In this example we are joining between the Sales.SalesOrderDetail and Production.Product tables.  The tables are aliased with the following:  SOD for Sales.SalesOrderDetail and P for Production.Product.  The JOIN logic is based on matching records in the SOD.ProductID and P.ProductID columns.  The records are filtered by only returning records with the SOD.UnitPrice greater than 1000.  Finally, the result set is returned in order with the most expensive first based on the ORDER BY clause and only the highest 100 products based on the TOP clause.

USE AdventureWorks; GO SELECT  TOP 100 P.ProductID,  P.Name,  P.ListPrice,  P.Size,  P.ModifiedDate,  SOD.UnitPrice,  SOD.UnitPriceDiscount, SOD.OrderQty, SOD.LineTotal FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product P  ON SOD.ProductID = P.ProductID WHERE SOD.UnitPrice > 1000 ORDER BY SOD.UnitPrice DESC GO

Employee Table :- Department Table:-

EmployeeID EmployeeName DepartmentID DepartmentID DepartmentName
1 Smith 1   1 HR
2 Jack 2    2 Finance
3 Jones 2    3 Security
4 Andrews 3  4 Sports
5 Dave 5     5 HouseKeeping
6 Jospeh    6 Electrical

inner Join

An Inner Join will take two tables and join them together based on the values in common columns ( linking field ) from each table.

Example 1 :- To retrieve only the information about those employees who are assinged to a department.

Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee Inner Join Department on Employee.DepartmentID = Department.DepartmentID

he ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping

Example 2:- Retrieve only the information about departments to which atleast one employee is assigned.

Select Department.DepartmentID,Department.DepartmentName From Department Inner Join Employee on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName
1 HR
2 Finance
3 Security
5 HouseKeeping

Outer Joins :-

Outer joins can be a left, a right, or full outer join.
Left outer join selects all the rows from the left table specified in the LEFT OUTER JOIN clause, not just the ones in which the joined columns match.

Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.


Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee LEFT OUTER JOIN Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping
6 Jospeh
Right outer join selects all the rows from the right table specified in the RIGHT OUTER JOIN clause, not just the ones in which the joined columns match.


Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available.

Select Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName From Employee Outer Join Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName EmployeeName
1 HR Smith
2 Finance Jack
2 Finance Jones
3 Security Andrews
4 Sports NULL
5 HouseKeeping Dave
6 Electrical NULL

This query will result in Null value for Employee Name where no Employee is assigned to that department.

 

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.

NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO