Intersections and Differences in Sets of Records

Introduction

Two of the intermediate operations used in SQL consists of finding out whether two tables or lists have common values or records in certain fields, or what records are in one table but not in the other.

Practical Learning: Introducing Intersection of Records

1. Open the LPM2 file. Select and copy its whole content
2. Start Microsoft SQL Server and connect
3. Right-click the name of the server and click New Query
4. Paste the LPM2 code in the Query Editor
5. To execute, right-click inside the Query Editor and click Execute
6. Click inside the Query Editor and press Ctrl + A
7. To see the list of tenants from the Rockville apartments and the list of tenants from Alexandria apartments, type the following code:
USE LPM2;
GO
SELECT *
FROM Rentals.Tenants t;
GO
SELECT *
FROM Rentals.Customers c;
GO
8. To execute, right-click inside the Query Editor and click Execute. Notice that, although both tables have columns with similar values, the orders of the columns with like-values are not the same on both tables:

Intersection in Sets of Records

If you have two tables with the same categories of information, you may want to know what records are common in the lists. For example, if you have two list of customers, you may want to know whether some customers are in both tables. That operation is called an intersection. The SQL performs it as done in algebra. That SQL operator is called INTERSECT. Its formula is:

{ <query_specification> | ( <query_expression> ) }
INTERSECT
{ <query_specification> | ( <query_expression> ) }

The INTERSECT keyword is written between two SQL expressions. The first expression is also referred to as the left expression. The second expression is also referred to as the right expression.

There are rules the expressions must follow:

 Both expressions must have the same number of columns The data types of the columns in the same positions must be compatible. This means that the data type of the column ColA in position Pn in table A must be compatible with the colum ColB in position Pn of table B. In the same way, the data type of the column ColX in position Pn+1 in table A must be compatible with the colum ColY in position Pn+1 of table B. Compatibilty means that both columns can be integer-based, or decimal-based, or string-based, or date-based

The columns in both expressions don't have to have the same name.

 Practical Learning: Finding Records Intersections
1. If you specify only one column in each expression, if a record in the left expression and another record in the right table have the same values, the value would appear in the result.
Click inside the Query Editor and press Ctrl + A
2. To find the intersection of records using one column, type the following:
USE LPM2;
GO
SELECT t.TenantCode
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber
FROM Rentals.Customers c;
GO
3. To execute, right-click inside the Query Editor and click Execute

4. The result of the INTERSECT operation consists of the names of the columns of the first expression. Therefore, if you want to specify an alias for a column, use the field of the first expression. As an example, change the statement as follows:
USE LPM2;
GO
SELECT t.TenantCode [Tenant Account #]
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber
FROM Rentals.Customers c;
GO
5. If you specify only one column in each expression, if no record in the left and right expression is the same, the result would be empty.
Click inside the Query Editor and press Ctrl + A
6. Type the following:
USE LPM2;
GO
SELECT t.PhoneNumber
FROM Rentals.Tenants t
INTERSECT
SELECT c.Phone
FROM Rentals.Customers c;
GO
7. To execute, right-click inside the Query Editor and click Execute

8. If you specify more than one column in each expression, if all columns in a certain position Pn of both expressions have the same value, the value would appear in the result. The database engine would check the values of columns in position Pn+1 of both expressions and those value must be the same.
Click inside the Query Editor and press Ctrl + A
9. Type the following:
USE LPM2;
GO
SELECT t.TenantCode, t.FirstName, t.MaritalStatus
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber, c.FirstName, c.Status
FROM Rentals.Customers c;
GO
10. To execute, right-click inside the Query Editor and click Execute

11. If you specify more than one column in each expression, if a column in position Pn of the left expression is different from the column in the same position Pn of the right expression, the whole result would be empty.
Click inside the Query Editor and press Ctrl + A
12. Type the following:
USE LPM2;
GO
SELECT t.TenantCode, t.FirstName, t.MaritalStatus
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber, c.FirstName, c.Status
FROM Rentals.Customers c;
GO
13. To execute, right-click inside the Query Editor and click Execute

14. Click inside the Query Editor and press Ctrl + A
15. You can use functions in the expressions. If you decide to do this
• You should (must) use the same (or compatible) function on both expressions
• The return values of the function on both expressions would be compared. If they are the same, the value would be included in the result, otherwise not
To see an example, type the following:
USE LPM2;
GO
SELECT tens.TenantCode [Tenant Account #],
CONCAT(tens.LastName, N', ', tens.FirstName) [Tenant Name]
FROM Rentals.Tenants tens
INTERSECT
SELECT custs.AccountNumber,
CONCAT(custs.LastName, N', ', custs.FirstName)
FROM Rentals.Customers custs;
GO
16. To execute, press F5

17. To see more examples, type the following:
USE LPM2;
GO
SELECT t.TenantCode, t.LastName
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber, c.LastName
FROM Rentals.Customers c;
GO
18. To execute, right-click inside the Query Editor and click Execute
19. Click inside the Query Editor and press Ctrl + A
20. To try another example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber, regs.RentStartDate
FROM Rentals.Registrations regs
INTERSECT
SELECT allocs.AllocationID, allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs;
GO
21. To execute, right-click inside the Query Editor and click Execute
22. Click inside the Query Editor and press Ctrl + A
23. To see another example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber,
regs.TenantCode,
regs.PropertyNumber,
regs.RentStartDate
FROM Rentals.Registrations regs
INTERSECT
SELECT allocs.AllocationID,
allocs.AccountNumber,
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs;
GO
24. To execute, right-click inside the Query Editor and click Execute

25. Click inside the Query Editor and press Ctrl + A
26. Instead of using the columns directly, if the expressions include foreign keys whose values are not very clear (such as the case of integers), you can use a join to present more meaningful values. If you decide t doo that
• You must use the join on both expressions
• The values in the joining records would be compared and they must be the exact same
To see an example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber,
CONCAT(tens.LastName, N', ', tens.FirstName) Tenant,
regs.PropertyNumber,
regs.RentStartDate
FROM Rentals.Registrations regs INNER JOIN Rentals.Tenants tens
ON regs.TenantCode = tens.TenantCode
INTERSECT
SELECT allocs.AllocationID,
CONCAT(custs.LastName, N', ', custs.FirstName),
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs INNER JOIN Rentals.Customers custs
ON allocs.AccountNumber = custs.AccountNumber;
GO
27. To execute, right-click inside the Query Editor and click Execute

28. Click inside the Query Editor and press Ctrl + A
29. To see an example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber,
tens.FirstName,
regs.PropertyNumber,
regs.RentStartDate
FROM Rentals.Registrations regs INNER JOIN Rentals.Tenants tens
ON regs.TenantCode = tens.TenantCode
INTERSECT
SELECT allocs.AllocationID,
custs.FirstName,
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs INNER JOIN Rentals.Customers custs
ON allocs.AccountNumber = custs.AccountNumber;
GO
30. To execute, right-click inside the Query Editor and click Execute

31. Click inside the Query Editor and press Ctrl + A
32. To view another example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber,
regs.RegistrationDate,
regs.PropertyNumber,
regs.RentStartDate
FROM Rentals.Registrations regs
INTERSECT
SELECT allocs.AllocationID,
allocs.DateAllocated,
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs;
GO
33. To execute, press F5
34. To get a more meaning display, change the SQL statement as follows:
USE LPM2;
GO
SELECT regs.RegistrationNumber [Registration #],
Format(regs.RegistrationDate, N'dddd, MMMM dd, yyyy')   [Allocated On],
N'Prop #: ' + props.PropertyNumber + N', Monthly Rent: ' + FORMAT(props.MonthlyRate, N'C') [Unit Info],
Format(regs.RentStartDate, N'y') [Property Occupied From]
FROM Rentals.Registrations regs INNER JOIN Listing.Properties props
ON regs.PropertyNumber = props.PropertyNumber
INTERSECT
SELECT allocs.AllocationID,
Format(allocs.DateAllocated, N'dddd, MMMM dd, yyyy'),
N'Prop #: ' + props.PropertyNumber + N', Monthly Rent: ' + FORMAT(props.MonthlyRate, N'C'),
Format(allocs.DateOccupiedFrom, N'y')
FROM Rentals.Allocations allocs INNER JOIN Listing.Properties props
ON allocs.ApartmentNumber = props.PropertyNumber;
GO
35. To execute, press F5

36. Click inside the Query window and press Ctrl + A
37. To see more examples, type the following:
USE LPM2;
GO
SELECT t.TenantCode, t.LastName
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber, c.LastName
FROM Rentals.Customers c;
GO
38. To execute, right-click inside the Query Editor and click Execute
39. To see one more example, type the following:
USE LPM2;
GO
SELECT pmts.PaymentNumber, pmts.PaymentDate,
pmts.RegistrationNumber, pmts.AmountPaid
FROM Rentals.Payments pmts
INTERSECT
recs.PaymentForAllocationNumber, recs.PaymentAmt
FROM Rentals.Receipts recs
ORDER BY pmts.RegistrationNumber;
GO
40. To execute, right-click inside the Query Editor and click Execute

41. Click inside the Query Editor, press Ctrl + A, and press Delete

Difference in Sets of Records

Once again, imagine you have two tables that have the same categories of records, such as two lists of customers. You may want to know what records belong to one list and not to the other list. To assist you with this operation, Transact-SQL provides the EXCEPT operator. Its formula is the same as that of INTERSECT:

{ <query_specification> | ( <query_expression> ) }
EXCEPT
{ <query_specification> | ( <query_expression> ) }

In this case also, the EXCEPT keyword is surrounded by two SQL expressions. The rules are the same for the intersection:

• The number of columns in both expressions must be the same
• Based on their positions, the data types of the columns of both expressions must be compatible

This time also, the names of the columns of the expressions can be different but columns of the first expression would be used for the final result. When creating the resulting list, the database engine compares the records of the left expression to those of the right expression:

• The first or left expression is the source and the second or right expression is the target
• If the source expression contains a record that cannot be found in the target expression, that record is added to the result
• If you reverse the source and target expressions, the comparison also is reversed
 Practical Learning: Finding the Difference in Records
1. To find an intersection of records, type the following:
USE LPM2;
GO
SELECT t.TenantCode
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber
FROM Rentals.Customers c;
GO
2. To execute, right-click inside the Query Editor and click Execute

3. To apply the exception, change the statement as follows:
USE LPM2;
GO
SELECT t.TenantCode
FROM Rentals.Tenants t
EXCEPT
SELECT c.AccountNumber
FROM Rentals.Customers c;
GO
4. To execute, right-click inside the Query Editor and click Execute

5. Notice that the resulting values are different.
To reverse the source and the target expressions, change the statement as follows:
USE LPM2;
GO
SELECT c.AccountNumber
FROM Rentals.Customers c
EXCEPT
SELECT t.TenantCode
FROM Rentals.Tenants t;
GO
6. To execute, press F5

7. Click inside the Query Editor and press Ctrl + A
8. Type the following:
USE LPM2;
GO
SELECT t.TenantCode [Account #], t.FirstName [First Name]
FROM Rentals.Tenants t
INTERSECT
SELECT c.AccountNumber, c.FirstName
FROM Rentals.Customers c;
GO
9. To execute, right-click inside the Query Editor and click Execute

10. Change the statement as follows:
USE LPM2;
GO
SELECT t.TenantCode [Account #], t.FirstName [First Name]
FROM Rentals.Tenants t
EXCEPT
SELECT c.AccountNumber, c.FirstName
FROM Rentals.Customers c;
GO
11. To execute, right-click inside the Query Editor and click Execute

12. Reverse the source and the target as follows:
USE LPM2;
GO
SELECT c.AccountNumber [Account #], c.FirstName [First Name]
FROM Rentals.Customers c
EXCEPT
SELECT t.TenantCode, t.FirstName
FROM Rentals.Tenants t;
GO
13. To execute, right-click inside the Query Editor and click Execute

14. Click inside the Query Editor and press Ctrl + A
15. To view another example, type the following:
USE LPM2;
GO
SELECT regs.RegistrationNumber,
regs.RegistrationDate,
regs.PropertyNumber,
regs.RentStartDate
FROM Rentals.Registrations regs
INTERSECT
SELECT allocs.AllocationID,
allocs.DateAllocated,
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs;
GO
16. To execute, press F5

17. To get the except result, change the statement as follows:
USE LPM2;
GO
SELECT regs.RegistrationNumber [Registration #],
regs.RegistrationDate   [Allocated On],
regs.PropertyNumber     [Unit Info],
regs.RentStartDate      [Property Occupied From]
FROM Rentals.Registrations regs
EXCEPT
SELECT allocs.AllocationID,
allocs.DateAllocated,
allocs.ApartmentNumber,
allocs.DateOccupiedFrom
FROM Rentals.Allocations allocs;
GO
18. To get a more meaning display, change the SQL statement as follows:
USE LPM2;
GO
SELECT regs.RegistrationNumber [Registration #],
Format(regs.RegistrationDate, N'D')   [Allocated On],
N'Prop #' + props.PropertyNumber + N', Rate: ' +
FORMAT(props.MonthlyRate, N'C') + N'/Month' [Unit Info],
Format(regs.RentStartDate, N'MMMM yyyy') [Property Occupied From]
FROM Rentals.Registrations regs INNER JOIN Listing.Properties props
ON regs.PropertyNumber = props.PropertyNumber
EXCEPT
SELECT allocs.AllocationID,
Format(allocs.DateAllocated, N'D'),
N'Prop #' + props.PropertyNumber + N', Rate: ' +
FORMAT(props.MonthlyRate, N'C') + N'/Month' [Unit Info],
Format(allocs.DateOccupiedFrom, N'MMMM yyyy')
FROM Rentals.Allocations allocs INNER JOIN Listing.Properties props
ON allocs.ApartmentNumber = props.PropertyNumber;
GO
19. To execute, press F5

20. Click inside the Query window, press Ctrl + A, and press Delete