1 / 36

SQL Set Operators Oracle and ANSI Standard SQL

SQL Set Operators Oracle and ANSI Standard SQL. Lecture 7. SQL Set Operators Join Types. Set theory Set operators Anti-joins. SQL Set Operators Set Theory: Introduction. Axioms Symbols Empty Set Intersections Exclusions. SQL Set Operations Set Theory: Axioms.

kara
Download Presentation

SQL Set Operators Oracle and ANSI Standard SQL

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. SQL Set OperatorsOracle and ANSI Standard SQL Lecture 7

  2. SQL Set OperatorsJoin Types • Set theory • Set operators • Anti-joins

  3. SQL Set OperatorsSet Theory: Introduction • Axioms • Symbols • Empty Set • Intersections • Exclusions

  4. SQL Set OperationsSet Theory: Axioms • Axiom of extensionality: Two sets are the same if and only if they have the same elements. • Axiom of empty set: There is a set with no elements. You can use {} or  to denote this empty set. • Axiom of pairing: If x, y are sets, then so is { x, y }, a set containing x and y as its only elements. • Axiom of union: Every set has a union. That is, for any set x there is a set y whose elements are precisely the elements of the elements of x.

  5. SQL Set OperationsSet Theory: Axioms • Axiom of infinity: There exists a set x such that {} is in x and whenever y is in x, so is the union y U { y }. • Axiom of separation (or subset axiom): Given any set and any proposition P(x), there is a subset of the original set containing precisely those elements x for which P(x) holds. • Axiom of replacement: Given any set and any mapping, formally defined as a proposition P( x, y ) where P( x, y ) and P( x, z ) implies y = z, there is a set containing precisely the images of the original set's elements.

  6. SQL Set OperationsSet Theory: Axioms • Axiom of power set: Every set has a power set. That is, for any set x there exists a set y, such that the elements of y are precisely the subsets of x. • Axiom of regularity (or axiom of foundation): Every non-empty set x contains some element y such that x and y are disjoint sets. • Axiom of choice: (Zermelo's version) Given a set x of mutually disjoint nonempty sets, there is a set y (a choice set for x) containing exactly one element from each member of x.

  7. SQL Set OperationsSet Theory: Symbols  - given any  - there is  - there is a unique = - equals  - defined as / - negation when overlaying another symbol  - negation when preceding another symbol + - addition operator x - multiplication operator

  8. SQL Set OperationsSet Theory: Symbols  - is a member of  - is not a non-member of (is a member of)  - is not a member of  - is not a member of  - a subset of  - a superset of  - a proper subset of (less than the set)  - a proper superset of (more than the set)  - if and only if

  9. SQL Set OperationsSet Theory: Symbols  - intersection  - union  - and  - or  - such that  - empty set {} - empty set  - then () - holds for (e.g., P(A) P holds for A)

  10. SQL Set OperationsSet Theory: Applied Symbols • A,B,C | C  A  C  B  A  B =  • Given any sets A, B and C such that C is a subset of A and C is not an element of B, then the intersection of A and B is the empty set. • This is an example of a null intersection.

  11. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { d, e, f } • A,B,C | C  A  C  B  A  B =   C  {} • The first phrase states the assumption, the second phrase states the proof, and the third the conclusion – in this case, that there is no intersection between sets A and B.

  12. SQL Set OperationsSet Theory: Applied Symbols • A,B,C | A  B = C  C(C  A  C B) • Given any sets A, B and C such that A intersects B as C if and only if there is a C that holds C is an element of A and B. • This is an example of an intersection.

  13. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { c, d, e } • A,B,C | A  B = C  C(C  A  C B)  C  { c } • The first phrase states the assumption, the second phrase states the conclusions, and the third the condition or proof – in this case, that there is an intersection between sets A and B.

  14. SQL Set OperationsSet Theory: Applied Symbols • ? [ Extra Credit – Due tonight by midnight in email ] • Given any sets A and B, there is a superset of unique values formed by combining A and B. • This is an example of an union.

  15. SQL Set OperationsSet Theory: Applied Symbols • A,B | A x C = { ( a, b ) | a  A  b  B } • Given any sets A and B, there product is the result set of ordered pairs from the two sets. • This is an example of an Cartesian product.

  16. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { c, d, e } • A,B | A x C = { ( a, b ) | a  A  b  B } • The first phrase states the assumption, the second phrase states the conclusions, and the third the condition or proof – in this case, that there is no intersection between sets A and B.

  17. SQL Set OperationsSet Theory: Applied Symbols • ? [ Extra Credit – Due tonight by midnight in email ] • Given any sets A and B, there is superset of the two that resides in neither. • This is an example of an exclusion.

  18. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { c, d, e } • Left join – A,B,C,D | (A + D(A  B)) = C  C(C  A  C B) • Left join – A,B,C | C = { a, b, c }

  19. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { c, d, e } • Right join – A,B,C,D | (D(A  B) + B) = C  C(C  A  C  B) • Right join – A,B,C | C = { c, d, e }

  20. SQL Set OperationsSet Theory: Applied Symbols • A | A  { a, b, c } • B | B  { c, d, e } • Full outer join – A,B,C,D | (A + D(A  B) + B) = C  C(C  A  C B) • Full outer join – A,B,C | C = { a, b, c, d, e }

  21. SQL Set OperatorsSet Theory: Summary • Axioms • Symbols • Empty Set • Intersections • Exclusions

  22. SQL Set OperatorsSET Operators: Introduction • INTERSECT • UNION ALL • UNION • MINUS

  23. SQL Set OperationsSet Operations: INTERSECT • The INTERSECT operator enables you to create an equijoin between two copies of one table, or two tables. • The INTERSECT operator requires you to have two balanced SELECT statements, which means the same column name, number, and data type.

  24. SQL Set OperationsSet Operations: INTERSECT • Oracle & ANSI SQL Example SELECT a.column_name_x alias_a , a.column_name_y alias_b FROM table1 a INTERSECT SELECT b.column_name_z alias_a , 'string_literal' alias_b FROM table1 b;

  25. SQL Set OperationsSet Operations: UNION ALL • The UNION ALL operator enables you to create an result set from two copies of one table, or two tables. • The UNION ALL operator returns duplicates for any intersecting rows. • The UNION ALL operator requires you to have two balancedSELECT statements, which means the same column name, number, and data type.

  26. SQL Set OperationsSet Operations: UNION ALL • Oracle & ANSI SQL Example SELECT a.column_name_x alias_a , a.column_name_y alias_b FROM table1 a UNION ALL SELECT b.column_name_z alias_a , 'string_literal' alias_b FROM table1 b;

  27. SQL Set OperationsSet Operations: UNION • The UNION operator enables you to create an result set from two copies of one table, or two tables. • The UNION operator performs an incremental sort operation to eliminate duplicates for any intersecting rows. • The UNION operator requires you to have two balanced SELECT statements, which means the same column name, number, and data type.

  28. SQL Set OperationsSet Operations: UNION ALL • Oracle & ANSI SQL Example SELECT a.column_name_x alias_a , a.column_name_y alias_b FROM table1 a UNION SELECT b.column_name_z alias_a , 'string_literal' alias_b FROM table1 b;

  29. SQL Set OperationsSet Operations: MINUS • The MINUS operator enables you to create an result set from two copies of one table, or two tables. • The MINUS subtracts set matching rows found in set B from set A, eliminating these rows from the result (a proper subset of A). • The MINUS operator requires you to have two balanced SELECT statements, which means the same column name, number, and data type.

  30. SQL Set OperationsSet Operations: UNION ALL • Oracle & ANSI SQL Example SELECT a.column_name_x alias_a , a.column_name_y alias_b FROM table1 a MINUS SELECT b.column_name_z alias_a , 'string_literal' alias_b FROM table1 b;

  31. SQL Set OperationsAnti-Join • The anti-join looks for all non-matches between two copies of one table, or two tables. • The anti-join can be resolve by using: • An non-equijoin operator – negation • A combination of set UNION, MINUS and INTERSECT operators. • The set operations require you to have two balanced SELECT statements, which means the same column name, number, and data type.

  32. SQL Set OperationsAnti-Join • Oracle Example SELECT a.column_name_x , a.column_name_y FROM table1 a, table2 b WHERE a.column_name_x <> b.column_name_z;

  33. SQL Set OperationsAnti-Join • ANSI SQL Example SELECT a.column_name_x , a.column_name_y FROM table1 a CROSS JOIN table2 b WHERE a.column_name_x <> b.column_name_z;

  34. SQL Set OperationsAnti-Join • Oracle & ANSI SQL Example SELECT <some columns> FROM table1 a UNION SELECT <some_columns> FROM table2 b MINUS SELECT <some columns> FROM table1 a INTERSECT SELECT <some_columns> FROM table2 b;

  35. SQL Set OperatorsSET Operators: Summary • INTERSECT • UNION ALL • UNION • MINUS

  36. Summary • Set theory • Axioms • Symbols • Empty Set • Intersections • Exclusions • Set operators • INTERSECT • UNION ALL • UNION • MINUS • Anti-joins

More Related