1 / 117

Chapter 6: Set Operators

5. Chapter 6: Set Operators. 5. Chapter 6: Set Operators. Objectives. Describe SQL set operators and modifiers. Types of Set Operators. Except. Union. Intersect. Outer Union. Set operators vertically combine rows from two result sets. There are four set operators:.

ltapia
Download Presentation

Chapter 6: Set Operators

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. 5 Chapter 6: Set Operators

  2. 5 Chapter 6: Set Operators

  3. Objectives • Describe SQL set operators and modifiers.

  4. Types of Set Operators Except Union Intersect Outer Union Set operators vertically combine rows from two result sets. There are four set operators:

  5. Default Behavior of Set Operators Columns are matched by position and must be the same data type. Column names in the final result set are determined by the first result set. EXCEPT INTERSECT UNION All columns from both result sets are selected. OUTER UNION

  6. Set Operator Syntax SELECT … EXCEPT | INTERSECT | UNION | OUTER UNION<CORR> <ALL> SELECT … General form of an SQL query using a set operator: The set operator operates on the result sets produced by the two SELECT statements, not on the actual tables themselves.

  7. Types of Set Operators select * from one except select * from two; ... EXCEPT Unique rows from the first table that are not found in the second table are selected.

  8. Types of Set Operators select * from one intersect select * from two; ... INTERSECT Common unique rows from both tables are selected.

  9. Types of Set Operators select * from one union select * from two; ... UNION Unique rows from both tables are selected with columns overlaid.

  10. Types of Set Operators select * from one outer union select * from two; • OUTER UNION • All rows from both tables, unique as well as non-unique, are selected. • Columns are not overlaid.

  11. Modifiers • You can use two modifiers to modify the behavior of set operators: • ALL • CORRESPONDING

  12. Modifiers • ALL • does not remove duplicate rows, and thus avoids an extra pass through the data. Use the ALL modifier for better performance when it is possible. • is not allowed in connection with an OUTER UNION operator. It is implicit.

  13. Modifiers • CORRESPONDING • overlays columns by name, instead of by position • removes any columns not found in both tables when used in EXCEPT, INTERSECT, and UNION operations • causes common columns to be overlaid when used in OUTER UNION operations • can be abbreviated as CORR.

  14. 6.01 Poll By default the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query output.  True  False

  15. 6.01 Poll – Correct Answer By default the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query output.  True  False

  16. 5 Chapter 6: Set Operators

  17. Objectives • Describe the SQL process when you use the EXCEPT set operator and keywords. • Use the EXCEPT set operator.

  18. EXCEPT Unique rows from the first result set that are not found in the second result set are selected.

  19. Business Scenario Considerations: • The orion.Employee_organization table contains information about all current Orion Star employees. • The orion.Sales table contains information about current Sales employees only. Create a report that displays the employee identification number and job title of the non-Sales staff employees.

  20. The EXCEPT Operator non-sales orion.Employee_organization orion.Sales You need a query that returns information from rows that exist in orion.Employee_organization, but not in orion.Sales. The EXCEPT operator could be useful.

  21. Flow Diagram: EXCEPT Operator EXCEPT Yes CORR Remove nonmatchingcolumns. No ALL No Yes Remove duplicate rows. Remove matching rows. End

  22. The EXCEPT Operator • Display the unique rows in Table ONE that are not found in Table TWO. select * from one except select * from two; s106d01 ...

  23. The EXCEPT Operator select * from one except select * from two; s106d01 ... The SQL processor removes duplicate rows within the tables.

  24. The EXCEPT Operator select * from one except select * from two; s106d01 ... The SQL processor creates an intermediate result setby returning the rows that are found only in Table ONE.

  25. The EXCEPT Operator • The column names are determined by Table ONE in the final result set. select * from one except select * from two; s106d01 ...

  26. The EXCEPT Operator with ALL select * from one except all select * from two; s106d02 ... Display the rows (duplicates included) that are found in Table ONE, but not in Table TWO.

  27. The EXCEPT Operator with ALL • The SQL processor creates an intermediate result set by returning the rows that are found only in Table ONE. select * from one except all select * from two; s106d02 ...

  28. The EXCEPT Operator with ALL select * from one except all select * from two; s106d02 ... The column names are determined by Table ONEin the final result set.

  29. The EXCEPT Operator with CORR select * from one except corr select * from two; s106d03 ... Display the unique rows that exist in Table ONE andnot in Table TWO, based on same-named columns.

  30. The EXCEPT Operator with CORR • The SQL processor eliminates any columns not found in both tables. select * from one except corr select * from two; s106d03 ...

  31. The EXCEPT Operator with CORR • The SQL processor eliminates duplicate rows. select * from one except corr select * from two; s106d03 ...

  32. The EXCEPT Operator with CORR • The SQL processor creates an intermediate result set by returning rows that are found only in Table ONE. select * from one except corr select * from two; s106d03 ...

  33. The EXCEPT Operator with CORR • The SQL processor creates an intermediate result set by returning rows that are found only in Table ONE. select * from one except corr select * from two; s106d03

  34. 6.02 Quiz select * from alpha except all corr select * from beta; s106a01 What are the results when you combine ALL with CORR? Run the program s106a01 and review the results.

  35. 6.02 Quiz – Correct Answer select * from alpha except all corr select * from beta; Final result set What are the results when you combine ALL with CORR?

  36. Step 1: Using ALL with CORR Step 1 select * from alpha except all corr select * from beta; ... CORR specifies that only same-named columns be used. ALL specifies that all values of X be used, including duplicates.

  37. Step 2: Using ALL with CORR Step 2 select * from alpha except all corr select * from beta; Final result set EXCEPT specifies that only X values found in ALPHA and not in BETA are used.

  38. Business Scenario (Review) orion.Employee_organization non-sales proc sql; select Employee_ID, Job_Title fromorion.Employee_organization except all select Employee_ID, Job_Title from orion.Sales; quit; orion.Sales s106d04 Create a report that displays the employee identification number and job title of the employees who are not Sales staff.

  39. The EXCEPT Operator Non-Sales Staff Employees Employee_ID Job_Title ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 120101 Director 120104 Administration Manager 120105 Secretary I 120106 Office Assistant II 120107 Office Assistant III 120108 Warehouse Assistant II 120109 Warehouse Assistant I 120110 Warehouse Assistant III 120111 Security Guard II 120112 Security Guard I Partial PROC SQL Output (10 out of 259)

  40. 6.03 Quiz select Employee_ID, Job_Title from orion.Employee_organization except all select Employee_ID, Job_Title from orion.Sales; s106a02 Answer the questions about the following program: Why is the CORR keyword not used in this example? 2. Would adding the CORR keyword to this example change the outcome?

  41. 6.03 Quiz – Correct Answer select Employee_ID, Job_Title from orion.Employee_organization except all select Employee_ID, Job_Title from orion.Sales; Answer the questions about the following program: Why is the CORR keyword not used in this example? Both SELECT lists specify the same column names in the same order, so CORR is not necessary.

  42. 6.03 Quiz – Correct Answer select Employee_ID, Job_Title from orion.Employee_organization except all select Employee_ID, Job_Title from orion.Sales; Answer the questions about the following program: Why is the CORR keyword not used in this example? Both SELECT lists specify the same column names in the same order, so CORR is not necessary. 2. Would adding the CORR keyword in this example change the outcome? No, adding CORR produces the same results.

  43. Using the CORR Keyword s106d04a This demonstration illustrates the use of the CORR keyword with the EXCEPT set operator.

  44. The EXCEPT Operator proc sql; select count(*) 'No. Non-Sales Managers' from (select distinct Manager_ID from orion.Employee_organization except all select Employee_ID from orion.Sales) ; quit; A manager might have multiple direct reports, so use the DISTINCT keyword in the first part of the query. You can confidently use the ALL keyword because the first query returns distinct values, andthe Sales table contains no duplicate records. s106d05 This query can easily become an in-line view used to determine how many managers, who are not Sales staff, are employed at Orion Star.

  45. The EXCEPT Operator No. Non-Sales Managers ƒƒƒƒƒƒƒƒƒ 48 PROC SQL Output

More Related