Multi-Table SELECT Statements—Topics

1. Multi-Table SELECT Statements—Topics • Multi-Table SELECT Statements • Joining Tables in the WHERE Clause • How Multi-Table Queries are Executed • Tables to Include in the FROM Clause • Style in WHERE Clause Joins • Using INNER JOIN to join tables • Table Aliases • Self (Unary) Joins • Composite Primary/Foreign Key Joins

2. Multi-Table SELECT Statements • The rules of normalization in database design result in data being dispersed into a multitude of tables • Data in multiple tables must be routinely queried together to support business needs • Reassemble the data pertaining to a single event, person, etc., when that data is dispersed • Perform research (business intelligence) • SQL provides different mechanisms to temporarily join data from multiple tables to produce a result set.

3. Joining Tables • Two techniques for joining tables require that the primary key values in one table equal the foreign key values in the related table. • Only combinations of records where this is true are included in the result set

4. Joining Tables in the WHERE Clause • Set PK = FK in the WHERE clause • List all output columns from any table in the query • List all tables needed in the query • Set PK = FK for all related tables in the query 1 SELECT Categories.CategoryID, CategoryName, ProductID, ProductName FROM Categories, Products WHERE Categories.CategoryID = Products.CategoryID ORDER BY CategoryID, ProductID 2 3

5. Joining Tables in the WHERE Clause (cont.) SELECT Categories.CategoryID, CategoryName, ProductID, ProductName FROM Categories, Products WHERE Categories.CategoryID = Products.CategoryID ORDER BY CategoryID, ProductID • If a column name exists in more than one table used in the query it must be fully qualified in the query • TableName.ColumnName • Any column can be written this way (and some developers will always qualify all columns, even in a single-table query)

6. Multi-Table Logic • The default (unenhanced) behavior of a multi-table query is to consider every combination of all rows in all included tables • When the WHERE clause is true for all criteria the combination of rows is added to the result set

7. Multi-Table Logic (cont.) COUNT() is anaggregate functionthat returns the number of recordsin the result set • Run all four of these queries at once SELECT COUNT(*) AS CountOfCategories FROM Categories SELECT COUNT(*) AS CountOfProducts FROM Products SELECT COUNT(*) AS CountOfJoin FROM Categories, Products WHERE Categories.CategoryID = Products.CategoryID SELECT COUNT(*) AS CountOfUnJoined FROM Categories, Products What do these results mean?

8. Multi-Table Logic (cont.) • Additional row-limiting WHERE expressions are also allowed SELECT COUNT(*) AS CountOfJoinBelow3 FROM Categories, Products WHERE Categories.CategoryID = Products.CategoryID AND Categories.CategoryID < 3 SELECT Categories.CategoryID, CategoryName, ProductID, ProductName FROM Categories, Products WHERE Categories.CategoryID = Products.CategoryID AND Categories.CategoryID < 3 Try changing this to Products.CategoryID

9. Exercises • Query the order date and contact information for the customer that placed Order # 10331 • List the product name and Supplier contact information for all products where the units on hand is less than the reorder point(What would be the most useful query to run in this case? Look at the Products table structure carefully.) • List the customer contact information for all customers who have purchased Northwoods Cranberry Sauce in 1997Note: The Order Details table must be written with square brackets→[Order Details]

10. Tables in the FROM Clause • A table must be included in the FROM clause if • It contributes columns specified in the SELECT clause • It has columns tested in the WHERE clause • It is needed to connect any tables required for the first two reasons above • Only list a table once even if it is used in the SELECT and WHERE clauses • Set all PK = FK criteria for all relationships between all tables in the FROM clause

11. Style in WHERE Clause Joins • When using the WHERE clause join in conjunction with other WHERE clause expressions • Put all joining WHERE clause expressions together • Put all row limiting WHERE clause expressions together • I don't care which you put first … • … just don't intermix them

12. Joining Tables using INNER JOIN • This query achieves results identical to the query on Slide #4 SELECT Categories.CategoryID, CategoryName, ProductID, ProductName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID ORDER BY Categories.CategoryID, ProductID

13. Joining Tables using INNER JOIN (cont.) • As many tables as necessary can be joined using this syntax • The INNER JOIN syntax executes more efficiently than the WHERE clause joins SELECT Categories.CategoryID, CategoryName, ProductID, ProductName, CompanyName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID ORDER BY Categories.CategoryID, ProductID

14. Exercises • List all of the product names from German suppliers • List the order date and the name of the shipping company used for all orders placed in May of 1998 • List the names and order dates of all products that employee Robert King has sold

15. Table Aliases • Tables can be aliased in a query and then referenced by their alias name instead of the whole table name SELECT Cat.CategoryID, CategoryName, ProductID, ProductName, CompanyName FROM Categories AS Cat INNER JOIN Products AS Prod ON Cat.CategoryID = Prod.CategoryID INNER JOIN Suppliers AS Sup ON Prod.SupplierID = Sup.SupplierID ORDER BY Cat.CategoryID, ProductID The AS is optionalCategories AS Cat and Categories Cat are equivalent

16. Table Aliases (cont.) • Many computer science professionals will alias every table with single character aliases • I do not like single-character aliases as they interfere with readability, especially in lengthy SQL • I have reviewed 64-page SQL statements with tables aliased in the first five pages and then referenced again twenty and forty pages later • I prefer that you not use aliases without a good reason

17. Self Joins • Look at the NorthwindEmployees table • ReportsTo is a foreign key in aunary relationship between Employees • In any record, ReportsTo containsthe EmployeeID for the employee'ssupervisor

18. Self Joins (cont.) • Write the SQL to retrieve the name of Robert King's supervisor • Use different aliases to create two pointers into the same table • Now write the SQL to retrieve the names of the employees that Andrew Fuller supervises Alias names should give a hint to the role being filled SELECT Sup.FirstName, Sup.LastName FROM Employees Emp INNER JOIN Employees Sup ON Emp.ReportsTo = Sup.EmployeeID WHERE Emp.LastName = 'King' AND Emp.FirstName = 'Robert'

19. Composite Primary/Foreign Keys • Just use AND expressions to join tables on composite primary & foreign keys SELECT …FROM Course INNER JOIN Section ON Course.DeptCode = Section.DeptCode AND Course.CourseNumber = Section.CourseNumber WHERE … SELECT …FROM Course, Section WHERE Course.DeptCode = Section.DeptCode AND Course.CourseNumber = SectionCourseNumber —OR—

20. Function of the Day • The DateAdd( ) function adds a number of date/time increments to a datetime value to produce a new datetime value that many increments in the future or past • Format: DATEADD(datepart, number, date) • Examples • DATEADD(dd, 3, '5/20/2008') gives 5/23/2008 • DATEADD(mm, -1, GETDATE()) gives the day one month prior to the current date • Hint: Experiment with the use of the mm increment on dates such as January 31st, March 31st, etc.

21. Function of the Day (cont.) • See the list of allowable date part specifications on p. 601 in Vieira • This list works in DateDiff, DatePart, and DateAdd • Write a query to determine the date five days in the future of every OrderDate • Write a query to determine the date on which each employee will reach their 10th year of employment with the company.