Standard SQL Server views can help. When we encapsulate complex multi-table query logic in a view, any application that needs that data is then able to issue a much simpler query against the view, rather than a complex multi-JOIN query against the underlying tables. Views bring other advantages too. We can grant users SELECT permissions on the view, rather than the underlying tables, and use the view to restrict the columns and rows that are accessible to the user. We can use views to aggregate data in a meaningful way.
The query in Listing 1 joins five tables to get information such as the client name, the order number and date, the products and quantities ordered.
SELECT CUST.CustomerID ,
PER.FirstName ,
PER.LastName ,
SOH.SalesOrderID ,
SOH.OrderDate ,
SOH.[Status] ,
SOD.ProductID ,
PROD.Name ,
SOD.OrderQty
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product PROD
ON PROD.ProductID = SOD.ProductID
INNER JOIN Sales.Customer CUST
ON SOH.CustomerID = CUST.CustomerID
INNER JOIN Person.Person PER
ON PER.BusinessEntityID = CUST.PersonID;
