T-SQL - Querying multiple tables with Joins

  08 Mar 2019
  tsql

Note : The following scripts are the part of the course on edx titled as: Querying Data with Transact-SQL -
Querying Data with Transact-SQL
These queries works on AdventureWorks database and information regarding the same
can be accessed by visiting following link -
AdventureWorks Installation and configuration


/* Inner, Outer, Cross, Self Combining rows from multiple tables Primary Key and Foreign Key

Select ….. From Table 1 JOIN Table 2 ON COLUMN

Select …. From Table1, Table2 where

Inner Joins – Return rows where a match is found in both tables Join operator is = then it is also called equi join

Select emp.Name, ord.amount from employee as emp [INNER] JOIN sales as ord on emp.EmpID = ord.EmpID

By default it is Inner Join (Optional , default value of join)

-- Basic Inner Join (Inner is Optional)
select
  SalesLT.Product.Name As ProductName,
  SalesLT.ProductCategory.Name As Category
From
  SalesLT.Product  
  INNER JOIN SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID
	-- Table Aliases
Select
  p.Name as ProductName,
  c.Name as Category
from
  SalesLT.Product As p
  Join SalesLT.ProductCategory as c on p.ProductCategoryID = c.ProductCategoryID
	-- Multiple Columns
Select
  oh.OrderDate,
  oh.SalesOrderNumber,
  p.Name as ProductName,
  od.OrderQty,
  od.UnitPrice,
  od.LineTotal
From
  SalesLT.SalesOrderHeader as oh
  join SalesLT.SalesOrderDetail as od on od.SalesOrderID = oh.SalesOrderID
  join SalesLT.Product as p on od.ProductID = p.ProductID
order by
  oh.OrderDate,
  oh.SalesOrderID,
  od.SalesOrderDetailID -- multiple tables with multiple predicates
Select
  oh.OrderDate,
  oh.SalesOrderNumber,
  p.Name as ProductName,
  od.OrderQty,
  od.UnitPrice,
  od.LineTotal
From
  SalesLT.SalesOrderHeader as oh
  join SalesLT.SalesOrderDetail as od on od.SalesOrderID = oh.SalesOrderID
  join SalesLT.Product as p on od.ProductID = p.ProductID
  and od.UnitPrice < p.ListPrice -- multiple predicates
order by
  oh.OrderDate,
  oh.SalesOrderID,
  od.SalesOrderDetailID

Outer Join - Return all rows from one table and any matching rows from second table Left, Right and F�ll, additional rows == NULL Left, RIght, and full by default are outer join, no need to specify Outer keyword

Select
  c.FirstName,
  c.LastName,
  oh.SalesOrderNumber
From
  SalesLT.Customer as c
  left outer join SalesLT.SalesOrderHeader as oh on c.CustomerID = oh.CustomerID
order by
  c.CustomerID
Select
  c.FirstName,
  c.LastName,
  oh.SalesOrderNumber
From
  SalesLT.Customer as c
  left outer join SalesLT.SalesOrderHeader as oh on c.CustomerID = oh.CustomerID
where
  oh.SalesOrderNumber Is NULL
Order By
  c.CustomerID
Select
  p.Name As ProductName,
  oh.SalesOrderNUmber
from
  SalesLT.Product as p
  left join SalesLT.SalesOrderDetail as od on p.ProductID = od.ProductID
  left join SalesLT.SalesOrderHeader as oh on od.SalesOrderID = oh.SalesOrderID
order by
  p.ProductID
Select
  p.Name as ProductName,
  c.Name as Category,
  oh.SalesOrderNumber
From
  SalesLT.Product as p
  Left Outer Join SalesLT.SalesOrderDetail as od on p.ProductID = od.ProductID
  Left outer join SalesLT.SalesOrderHeader as oh on od.SalesOrderID = oh.SalesOrderID
  inner join SalesLT.ProductCategory as c on p.ProductCategoryID = c.ProductCategoryID
order by
  p.ProductID

Cross Join Combine each row from first table with each row from second table Cartesian Product = Cross Join Logics for Inner and Outer Joins Inner join starts with cartesian product and adds filter Outer Join takes cartesian Product, filters and then add back non- matching rows with null

Select
  p.Name,
  c.FirstName,
  c.LastName,
  c.Phone
from
  SalesLT.Product as p Cross
  Join SalesLT.Customer as c

Self Join Compare rows in same table to each other

Select
  emp.Name as employee,
  man.Name as manager
From
  HR.Employee as emp
  Left Join HR.Employee as man on emp.ManagerID = man.EmployeeID