Learning T-SQL (Transact-SQL) - Unit 2

Transact-SQL 

INNER JOIN

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Examples -

1. Performs a join on a single matching column, relating the ProductModelID in the Production.Product table to the ProductModelID on the Production.ProductModel table:

SELECT p.ProductID, m.Name AS Model, p.Name AS Product
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS m
ON p.ProductModelID = m.ProductModelID
ORDER BY p.ProductID;

2. Inner join may be extended to include more than two tables. The Sales.SalesOrderDetail table is joined to the output of the JOIN between Production.Product and Production.ProductModel. Each instance of JOIN/ON does its own population and filtering of the virtual output table. 

SELECT od.SalesOrderID, m.Name AS Model, p.Name AS ProductName, od.OrderQty

FROM Production.Product AS p

INNER JOIN Production.ProductModel AS m

    ON p.ProductModelID = m.ProductModelID

INNER JOIN Sales.SalesOrderDetail AS od

    ON p.ProductID = od.ProductID

ORDER BY od.SalesOrderID;

OUTER JOIN

A FULL OUTER JOIN is used rarely. It returns all the matching rows between the two tables, plus all the rows from the first table with no match in the second, plus all the rows in the second without a match in the first.

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

Example -

SELECT emp.FirstName, ord.Amount

FROM HR.Employee AS emp

LEFT JOIN Sales.SalesOrder AS ord

    ON emp.EmployeeID = ord.EmployeeID;

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table.

• As with an INNER JOIN, an OUTER JOIN may be performed on a single matching column or on multiple matching attributes.
• Unlike an INNER JOIN, the order in which tables are listed and joined in the FROM clause does matter with OUTER JOIN, as it will determine whether you choose LEFT or RIGHT for your join.

SELECT p.Name As ProductName, c.Name AS Category, oh.SalesOrderNumberFROM SalesLT.Product AS p LEFT OUTER JOIN SalesLT.SalesOrderDetail AS od ON p.ProductID = od.ProductIDLEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh ON od.SalesOrderID = oh.SalesOrderIDINNER JOIN SalesLT.ProductCategory AS c ON p.ProductCategoryID = c.ProductCategoryIDORDER BY p.ProductID;

CROSS JOIN

A cross join is simply a Cartesian product of the two tables

Example - 

SELECT emp.FirstName, prd.Name

FROM HR.Employee AS emp

CROSS JOIN Production.Product AS prd;

SELF JOIN 

There may be scenarios in which you need to retrieve and compare rows from a table with other rows from the same table.  

The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.