Home

Intersections of Records
 

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 LearningPractical 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:

Finding Records Intersections

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:

Intersection in Sets of Records
  • 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 LearningPractical 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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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

    Finding Records Intersections

  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
     

    Finding Records Intersections

  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 LearningPractical 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
     

    Finding Records Intersections

  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

    Finding Records Exceptions

  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

    Finding Records Exceptions

  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
     

    Finding Records Exceptions

  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
     

    Finding Records Exceptions

  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
     

    Finding Records Exceptions

  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
     

    Finding Records Intersections

  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
     

    Finding Records Exceptions

  20. Click inside the Query window, press Ctrl + A, and press Delete
 
 
   
 
Previous Copyright © 2012-2013 FunctionX Next