1 / 33

A Guide to SQL, Seventh Edition

A Guide to SQL, Seventh Edition. Objectives. Use joins to retrieve data from more than one table Use the IN and EXISTS operators to query multiple tables Use a subquery within a subquery Use an alias. A Guide to SQL, Seventh Edition. Objectives. Join a table to itself

derex
Download Presentation

A Guide to SQL, Seventh Edition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. A Guide to SQL, Seventh Edition

  2. Objectives • Use joins to retrieve data from more than one table • Use the IN and EXISTS operators to query multiple tables • Use a subquery within a subquery • Use an alias A Guide to SQL, Seventh Edition

  3. Objectives • Join a table to itself • Perform set operations (union, intersection, and difference) • Use the ALL and ANY operators in a query • Perform special operations (inner join, outer join, and product) A Guide to SQL, Seventh Edition

  4. Querying Multiple Tables • When querying more than one table, the tables must be joined • Join tables by finding columns with matching data • Join tables by using a condition in the WHERE clause A Guide to SQL, Seventh Edition

  5. Joining Two Tables • In the SELECT clause, list all columns you want to display • In the FROM clause, list all tables involved in the query • In the WHERE clause, restrict to the rows that have common values in matching columns A Guide to SQL, Seventh Edition

  6. A Guide to SQL, Seventh Edition

  7. A Guide to SQL, Seventh Edition

  8. A Guide to SQL, Seventh Edition

  9. Comparing JOIN, IN, and EXISTS • Tables can be joined using IN or EXISTS clause • Use IN operator with a subquery • Use the EXISTS operator to retrieve data from more than one table A Guide to SQL, Seventh Edition

  10. A Guide to SQL, Seventh Edition

  11. A Guide to SQL, Seventh Edition

  12. A Guide to SQL, Seventh Edition

  13. A Guide to SQL, Seventh Edition

  14. Correlated Subquery • Subquery involves a table listed in the outer query • In Figure 4.7 the ORDERS table, listed in the FROM clause of the outer query, is used in the subquery • You need to qualify ORDER_NUM column in subquery as ORDERS.ORDER_NUM A Guide to SQL, Seventh Edition

  15. Correlated Subquery • For each row in the ORDERS table • Subquery executed using the value of ORDERS.ORDER_NUM that appears in the row • The inner query makes a list of rows in the ORDER_LINE table • Where ORDER_LINE.ORDER_NUM matches this value and • In which PART_NUM is equal to DR93 A Guide to SQL, Seventh Edition

  16. Using a Subquery within a Subquery • A nested subquery is a subquery within a subquery • SQL evaluates the queries from the innermost query to the outermost • It is possible that there is more than one approach to formulation of the queries • Many DMBS have optimizers that analyze queries for efficiency A Guide to SQL, Seventh Edition

  17. A Guide to SQL, Seventh Edition

  18. Using an Alias • An alias is an alternate name for a table • Used when tables are listed in the FROM clause • Created by typing the name of the table, hitting a space, then typing the name of the alias • Allows for simplicity A Guide to SQL, Seventh Edition

  19. Joining a Table to Itself • A self-join is when you are joining a table to itself • A second use for using an alias • Used when comparing records within one table • Alias allows you to treat one table as two separate tables A Guide to SQL, Seventh Edition

  20. A Guide to SQL, Seventh Edition

  21. Using a Self-Join on a Primary Key • It is possible to create a self-join that involves the primary key of the table • Just as in previous examples, you would list the table twice in the FROM clause with aliases A Guide to SQL, Seventh Edition

  22. A Guide to SQL, Seventh Edition

  23. Joining Several Tables • Condition shows how the columns are related for each pair of tables A Guide to SQL, Seventh Edition

  24. Joining Several Tables Step-By-Step • In the SELECT clause list all the columns to display • Qualify the column name if needed • In the FROM clause list all tables • Include tables used in the WHERE clause, even if they are not in the SELECT clause A Guide to SQL, Seventh Edition

  25. Joining Several Tables Step-By-Step • Take one pair of related tables at a time • Indicate in the WHERE clause the condition that relates the tables • Join conditions with the AND operator • Include any additional conditions in the WHERE clause • Connect them with the AND operator A Guide to SQL, Seventh Edition

  26. Set Operations • Set operations are used for taking the union, intersection, and differences of two tables • The union of two tables is a table containing every row that is in either the first table, the second table, or both tables A Guide to SQL, Seventh Edition

  27. Set Operations • The intersection (intersect) of two tables is a table containing all rows that are in both tables • The difference (minus) of two tables is the set of all rows that are in the first tables but are not in the second table A Guide to SQL, Seventh Edition

  28. Restrictions to Set Operations • Requirements for tables to be union compatible • Have the same number of columns AND • Their corresponding columns have identical data types and lengths A Guide to SQL, Seventh Edition

  29. Special Operations • Inner Join • A join that compares the tables in the FROM clause and lists on those rows that satisfy the condition in the WHERE clause • Outer Join • A join that lists all the rows from one of the tables in a join, regardless of matching A Guide to SQL, Seventh Edition

  30. Outer Joins • Left outer join: all rows from the table on the left (listed first in the query) will be included; matching rows only from the table on the right will be included • Right outer join: all rows from the table on the right will be included; matching rows only from the table on the left will be included • Full outer join: all rows from both tables will be included regardless of matches A Guide to SQL, Seventh Edition

  31. Product • The product (Cartesian Product) of two tables is the combination of all rows in the first table and all rows in the second table • Omit the WHERE clause to form a product A Guide to SQL, Seventh Edition

  32. A Guide to SQL, Seventh Edition

  33. Summary • Join tables with various methods • A subquery can contain another subquery • An alias can be used to simplify the SQL command as well to create self join • UNION, INTERSECT, MINUS commands are introduced • To form a product of two tables, include both tables in the FROM clause and omit the WHERE clause A Guide to SQL, Seventh Edition

More Related