SQL Set Operators Oracle and ANSI Standard SQL

SQL Set OperatorsOracle and ANSI Standard SQL Lecture 7

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

SQL Set Operators Oracle and ANSI Standard SQL