370 likes | 693 Views
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.
E N D
SQL Set OperatorsJoin Types • Set theory • Set operators • Anti-joins
SQL Set OperatorsSet Theory: Introduction • Axioms • Symbols • Empty Set • Intersections • Exclusions
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.
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.
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.
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
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
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)
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.
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.
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.
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.
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.
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.
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.
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.
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 }
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 }
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 }
SQL Set OperatorsSet Theory: Summary • Axioms • Symbols • Empty Set • Intersections • Exclusions
SQL Set OperatorsSET Operators: Introduction • INTERSECT • UNION ALL • UNION • MINUS
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.
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;
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.
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;
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.
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;
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.
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;
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.
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;
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;
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;
SQL Set OperatorsSET Operators: Summary • INTERSECT • UNION ALL • UNION • MINUS
Summary • Set theory • Axioms • Symbols • Empty Set • Intersections • Exclusions • Set operators • INTERSECT • UNION ALL • UNION • MINUS • Anti-joins