A. Using a simple FROM clause
The following example retrieves the TerritoryID and Name columns from the SalesTerritory table in the AdventureWorks sample database.
USE AdventureWorks ;
GO
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;
Here is the result set.
TerritoryID Name
----------- ------------------------------
1 Northwest
2 Northeast
3 Central
4 Southwest
5 Southeast
6 Canada
7 France
8 Germany
9 Australia
10 United Kingdom
(10 row(s) affected)
B. Using the TABLOCK and HOLDLOCK optimizer hints
The following partial transaction shows how to place an explicit shared table lock on Employee and how to read the index. The lock is held throughout the whole transaction.
USE AdventureWorks ;
GO
BEGIN TRAN
SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;
C. Using the SQL-92 CROSS JOIN syntax
The following example returns the cross product of the two tables Employee and Department. A list of all possible combinations of EmployeeID rows and all Department name rows are returned.
USE AdventureWorks ;
GO
SELECT e.EmployeeID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.EmployeeID, d.Name ;
D. Using the SQL-92 FULL OUTER JOIN syntax
The following example returns the product name and any corresponding sales orders in the SalesOrderDetail table. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.
USE AdventureWorks ;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL OR sod.ProductID IS NULL
ORDER BY p.Name ;
E. Using the SQL-92 LEFT OUTER JOIN syntax
The following example joins two tables on ProductID and preserves the unmatched rows from the left table. The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and not ordered, appear in the result set.
USE AdventureWorks ;
GO
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
F. Using the SQL-92 INNER JOIN syntax
The following example returns all product names and sales order IDs.
USE AdventureWorks ;
GO
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
G. Using the SQL-92 RIGHT OUTER JOIN syntax
The following example joins two tables on TerritoryID and preserves the unmatched rows from the right table. The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each table. All salespersons appear in the result set, whether or not they are assigned a territory.
USE AdventureWorks ;
GO
SELECT st.Name AS Territory, sp.SalesPersonID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;
H. Using HASH and MERGE join hints
The following example performs a three-table join among the Product, ProductVendor, and Vendor tables to produce a list of products and their vendors. The query optimizer joins Product and ProductVendor (p and pv) by using a MERGE join. Next, the results of the Product and ProductVendor MERGE join (p and pv) are HASH joined to the Vendor table to produce (p and pv) and v.
.gif) Important: |
|---|
|
After a join hint is specified, the INNER keyword is no longer optional and must be explicitly stated for an INNER JOIN to be performed.
|
USE AdventureWorks ;
GO
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.VendorID = v.VendorID
ORDER BY p.Name, v.Name ;
I. Using a derived table
The following example uses a derived table, a SELECT statement after the FROM clause, to return the first and last names of all employees and the cities in which they live.
USE AdventureWorks ;
GO
SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name,
d.City
FROM Person.Contact AS c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN
(SELECT ea.AddressID, ea.EmployeeID, a.City
FROM Person.Address AS a
INNER JOIN HumanResources.EmployeeAddress AS ea
ON a.AddressID = ea.AddressID) AS d
ON e.EmployeeID = d.EmployeeID
ORDER BY c.LastName, c.FirstName;
J. Using TABLESAMPLE to read data from a sample of rows in a table
The following example uses TABLESAMPLE in the FROM clause to return approximately 10 percent of all the rows in the Customer table in the AdventureWorks database.
USE AdventureWorks ;
GO
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
K. Using APPLY
The following example assumes that the following tables with the following schema exist in the database:
-
Departments: DeptID, DivisionID, DeptName, DeptMgrID
-
EmpMgr: MgrID, EmpID
-
Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary
There is also a table-valued function, GetReports(MgrID) that returns the list of all employees (EmpID, EmpLastName, EmpSalary) that report directly or indirectly to the specified MgrID.
The example uses APPLY to return all departments and all employees in that department. If a particular department does not have any employees, there will not be any rows returned for that department.
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;
If you want the query to produce rows for those departments without employees, which will produce null values for the EmpID, EmpLastName and EmpSalary columns, use OUTER APPLY instead.
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID) ;
L. Using PIVOT and UNPIVOT
The following example returns the number of purchase orders placed by employee IDs 164, 198, 223, 231, and 233, categorized by vendor ID.
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
Here is a partial result set:
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------------------------------------------------------------
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
To unpivot the table, assume the result set produced in the previous example is stored as pvt. The query looks like the following.
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) AS p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
Here is a partial result set:
VendorID Employee Orders
------------------------------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5