SQL Unit 6 Set Operations

1. SQL Unit 6Set Operations Kirk Scott

2. 6.1 Introduction • 6.2 UNION Queries • 6.3 Queries with IN (Intersection) • 6.4 Queries with NOT IN (Set Subtraction) • 6.5 Unions, Joins, and Outer Joins

3. 6.1 Introduction

4. 1. The technical term for a table is a relation. A relation is like a set. • The technical term for a row in a table is a tuple. • A tuple is like an element in a set. • The fundamental difference between an element of a set and a tuple in a relation is that the tuple may be a composite. • It may contain values for more than one different attribute.

5. The similarity between sets and relations explains some of the aspects of relations. • The order of elements in a set is immaterial; likewise, the order of tuples in a relation is immaterial. • A set can't contain duplicate elements; likewise, a relation can't contain duplicate tuples.

6. 2. Recall the logical operator OR. • This allowed you to make conditions on the values of attributes. • There is also a set level operation, union, which is related in meaning. • Union applies not to attribute values, but to collections of tuples in relations.

7. Given two sets, A and B, you may recall this definition of union from math class: • = the union of A and B • = the set of elements in A or in B or in both A and B

8. A B • In this Venn diagram, both A and B and the area where they overlap are shaded, indicating that they all are included in the union.

9. Microsoft Access SQL has the keyword UNION, which implements the idea behind a logical union.

10. 3. Here is a simple example illustrating the use of the union operator. • Suppose that table A and table B have each been defined with the same number of fields, of the same type, in the same order. • The names of the fields don't have to be the same.

11. Then consider this query: • SELECT * • FROM A • UNION • SELECT * • FROM B

12. The results will contain the set of rows that were in A or B or both, but because the query uses a set operator, and duplicates are not allowed in sets, any row that might have occurred in both A and B will only appear once in the results.

13. 4. The previous example specified that the tables in the two parts of the query had to have the same number of fields of the same type and in the same order. • It wouldn't do to have records in the same result table which varied in the number of fields they contained. • It also wouldn't do for numeric fields to hold non-numeric values, and vice-versa.

14. Having a correspondence between fields in the two parts of the query is known as union compatibility. • The specific requirements for this are: • A. The corresponding fields in the two parts of the query should mean the same thing. This may be referred to as semantic equivalence.

15. B. If the corresponding fields are of exactly the same type and size, there is no problem at all. • The formal requirements are less stringent though: • i. All numeric fields are union compatible with each other. • ii. All text fields are union compatible with each other. • iii. All date fields are union compatible with each other.

16. In cases where the types of the fields are not the same, but they are union compatible, the "larger" of the two types will be used in the results. • Given two union compatible types, one type can always hold values of the other type. • A text field with a large width can hold the values of a text field with a smaller width. • A numeric type that can have decimal points can hold integer values. • Since the one that can hold the other is used in the results, no data will be lost when a union is done.

17. 6.2 UNION Queries

18. 1. Here is a concrete example of a union query using tables and fields from the cardealership database: • SELECT * • FROM Car • WHERE make = 'Chevrolet' • UNION • SELECT * • FROM Car • WHERE make = 'Toyota'

19. This query illustrates the relationship between the UNION operator and the OR operator. • Because the two parts of the query are on the same table, the following query would accomplish the same thing: • SELECT * • FROM Car • WHERE make = 'Chevrolet' • OR make = 'Toyota'

20. In this query the Car table is the "universe", and the query finds the union of two disjoint subsets of the Car table, because no car could have two different makes at the same time. • This is the Venn diagram for the query:

21. Car make = 'Chevro-let' make = 'Toyota'

22. 2. Here is another example of a union query. • The two parts of the query are based on two different tables: • SELECT name, addr, city, state • FROM Customer • UNION • SELECT name, addr, city, state • FROM Salesperson

23. Because two tables are involved, it would not be possible to accomplish this with the OR operator. • Notice that there is no problem with union compatibility because the corresponding fields in the two tables were defined in exactly the same way.

24. The Venn diagram for this query is more typical than the previous diagram. • The same person could be both a salesperson and a customer. • The results of the query would include all customers, all salespeople, and anybody who fell into both categories.

25. Custom-ers Sales- people

26. A union can be thought of as a vertical combination of two tables:

27. UNION

28. 3. As noted previously, the union operator eliminates duplicates from the results of a query. • If by chance you would like to do a union and not have the duplicates eliminated, you would use the keywords UNION ALL: • SELECT city • FROM Customer • UNION ALL • SELECT city • FROM Salesperson

29. There is a side effect related to eliminating or keeping duplicates in the results. • When plain UNION is used, the duplicates will be eliminated and the results will typically be sorted in some order. • The explanation is that the system uses the following approach to eliminate duplicates: • First it sorts the records. After sorting, duplicates should be next to each other. • Then the system finds and eliminates them.

30. 4. It is possible to do unions where one part of the query doesn't have fields corresponding to the fields in the other part. • Those fields that correspond have to be union compatible. • For those fields without corresponding fields, nulls have to be used.

31. Recall that the schemas for Customer and Salesperson look like this: • Customer(custnopk, name, addr, city, state, phone) • Salesperson(spnopk, name, addr, city, state, phone, bossno, commrate)

32. Here is an example of a union query where all of the fields of the Customer table are matched with the explicitly listed corresponding fields of the Salesperson table: • SELECT * • FROM Customer • UNION • SELECT spno, name, addr, city, state, phone • FROM Salesperson

33. If you would like to keep all of the fields from the Salesperson table while also including all of the records from the Customer table in the results, you could do this: • SELECT *, NULL, NULL • FROM Customer • UNION • SELECT * • FROM Salesperson

34. 6.3 Queries with IN (Intersection)

35. 1. Among the concepts of set theory, along with union, there is intersection and there is the idea of set containment. • Given two sets, A and B, here is the definition of union again, along with the definitions of intersection and containment:

36. = the union of A and B = the set of elements in A or in B or in both A and B

37. = the intersection of A and B = the set of elements that A and B have in common

38. = A is contained in B; as a proposition this is either true or false, either the elements of A are also in B, or they're not

39. A B • In this Venn diagram, the area where A and B overlap is crosshatched, indicating that this is the area in the intersection.

40. B A • This Venn diagram signifies that A is contained in B:

41. Microsoft Access SQL does not have keywords for intersection or containment, but it does have this operator: • IN • Using IN it is possible to write expressions that check whether or not a given set of tuples is included in another set. • This makes it possible to find the intersection of two sets.

42. 2. It is possible to specify a set of values in SQL by enclosing the values in parentheses (not curly braces) and separating them with commas. • This first example of the use of the keyword IN involves such a set: • SELECT * • FROM Car • WHERE make IN ('Chevrolet', 'Toyota')

43. This query is equivalent in results to the following query already seen above: • SELECT * • FROM Car • WHERE make = 'Chevrolet' • OR make = 'Toyota' • The results of the query are the union of two sets.

44. 3. The more general use of the keyword IN occurs when a set of values in a query is defined by a subquery rather than a set listed in parentheses. • An example is shown below. • Notice that its structure is similar to the foregoing examples.

45. The outer query selects from a table where some field value is in or is not in the set specified by the subquery: • SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale)

46. This query illustrates the ideas of intersection and containment. • You're selecting the names of salespeople whose spno's appear in the Carsale table. • Because of referential integrity, every spno in the Carsale table has to appear in the Salesperson table. • That means that the set of spno's from the Carsale table is a subset of the spno's in the Salesperson table.

47. Not every salesperson has to have sold a car, so not necessarily every spno in Salesperson appears in the Carsale table. • When you find the intersection between the two, it is simply the set of spno's from Carsale.

48. Salesperson spno's— get the names from this table Carsale spno's • This is a Venn diagram of the situation:

49. The query finds the names of salespeople who sold cars. • Notice that because of the way this query is structured as a set query, a salesperson's name will appear only once in the results, even if that salesperson sold more than one car. • In other words, a given spno may occur more than once in the Carsale table.

50. However, in the outer query, when checking whether a given spno is in the set defined by the subquery, the answer is either yes or no. • If the answer is yes, then the spno is valid in the outer query, but it only appears there once. • The "IN" can be read as "squeezing out" duplicate occurrences of spno. • Then in the outer query, for each distinct spno, the one corresponding name is shown.