Joins in SQL server

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

1. Inner join
2. Outer join
3. Left outer join
4. Right outer join
5. Cross join
6. 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:

SELECT

Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName FROM Employee LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID

Right outer join example:

SELECT

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.

Example:
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.

Example: 

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