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 P_n 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 P_n+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 P_n of the left expression is different from the column in the same position P_n 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
    SELECT recs.ReceiptID, recs.DateReceiptMade,
           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

Previous	Copyright © 2012-2022, FunctionX	Thursday 26 May 2022	Next