Fundamentals of joins in SQL server
By using joins, we can get the data from two or more tables based on logical condition between the tables.
The two tables in a query related by
- specifying a column from each table used in the join.
- specifying a logical operator to be used in the comparing the columns.
The following are joins by classification
- Inner join
- Outer join
- Left outer join
- Right outer join
- Cross join
- Self join
Inner join: is a join which returns only the rows for which there is
an equal value in the join column. Inner joins can be specified in either the
FROM or WHERE caluses.
Recommended method is specify the join condition in FROM clause which helps in specifying the other conditions in the WHERE clause.
FROM <First_Table> <Join_Type> <Second_Table>
[ON <join-condition> ]
Example: SELECT *
FROM Employee AS e
INNER JOIN Department AS d
ON e.DepartmentID = d.DepartmentID
Inner join becomes an Equi join when you specify equality condition in JOIN clause.
Outer join: is a join which returns all the rows from the at least one of
the tables mentioned in the FROM clause and meeting the condition in the WHERE clause.
Outer joins can be specified in the FROM clause only.
All rows are retrieved from the left table referenced with a left outer join
and all rows retrieved from the right table with a right outer join.
All rows from the both tables are returned in a full outer join.
Left outer join example:
Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName FROM Employee LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Right outer join example:
Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName FROM Employee RIGHT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Cross join: A cross join is join which does not have a WHERE clause and
produces the cartesian product of the tables involved in the join.
The size of the cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second table.
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
Self-Joins: A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID