Microsoft SQL Server - Lesson 33: Unions of Records

	Unions of Records

Uniting Some Records

Introduction

Consider the following tables:

USE Exercise1;
GO

DROP TABLE HumanResources.Employees;
GO
DROP TABLE HumanResources.Contractors;
GO

CREATE TABLE HumanResources.Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    [Status] nvarchar(20) default N'Employee'
);
GO
CREATE TABLE HumanResources.Contractors
(
    ContractorCode nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2),
    [Type] nvarchar(20) default N'Contractor'
);
GO

INSERT INTO HumanResources.Employees(EmployeeNumber,
		FirstName, LastName, HourlySalary)
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO HumanResources.Contractors(ContractorCode, Name1, Name2, Wage)
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO

Sometimes, either for the sake of comparing records or for preparing them for any operation, you may want to display the records of more than one table. To support the ability to select records of various tables and show them together, you use the UNION operator. The basic formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION
SELECT WhatField(s) FROM AnotherTable;

There are rules you must follow:

Both tables must have the same number of columns
The sequence of data types of the columns in each table must be the same. For example, if the column in one table is string-based, the corresponding column, by order, in the other table must also be string-based
The data types in the order of the columns of both tables must be compatible. For example, if the first column of one table uses an integer-based data type, the first column of the other table must also have an integer-based data type that can be reconciled with the corresponding column of the other table

The columns don't have to have the same name. Here is an example of getting a union of all records from both tables:

SELECT * FROM HumanResources.Employees
UNION
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

If you use the above formula, the records of all SELECTed tables would be included in the result. As an alternative, you can set a condition for the table(s) to exclude some records. You can set a condition on only one table, some tables, or all tables, but each table must have its own condition. Here is an exmple that unites some records of the above employees whose name end with s and the contractors who earn more than 20.00:

SELECT * FROM HumanResources.Employees WHERE LastName LIKE N'%s'
UNION
SELECT * FROM HumanResources.Contractors WHERE Wage >= 20.00;
GO

This would produce:

Uniting ALL Records

Practical Learning: Uniting the Records

To see the records of tenants from Rockville and Alexandria previous databases, type the following code:
```
USE LPM2;
GO
SELECT * FROM Rentals.Tenants;
GO
SELECT * FROM Rentals.Customers;
GO
```
To execute, press F5
Click inside the Query Editor and press Ctrl + A

To unite the records of the tenants, type the following code:

USE LPM2;
GO
SELECT t.TenantCode, t.FirstName, t.LastName, 
       t.MaritalStatus, t.PhoneNumber, t.EmailAddress
FROM Rentals.Tenants t
UNION
SELECT c.AccountNumber, c.FirstName, c.LastName, c.[Status], c.Phone, c.Email
FROM Rentals.Customers c;
GO

Press F5 to execute. This would produce:
Click inside the Query Editor and press Ctrl + A

If you want, you can specify the captions you want. Change the code as follows:

USE LPM2;
GO
SELECT r.RegistrationNumber [Regist #],
       r.RegistrationDate   [Regist Date],
       r.TenantCode         [For Account #],
       r.PropertyNumber     [For Prop #],
       r.RentStartDate      [Rent Start On]
FROM Rentals.Registrations r
UNION
SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
       a.ApartmentNumber, a.DateOccupiedFrom
FROM Rentals.Allocations a;
GO

Press F5 to execute
Notice that you get 14 records.
Click inside the Query Editor
Press Ctrl + A and press Delete

Uniting ALL Records

By default, when UNION is used, the database engine arranges the records based on the first column. This means that if the first column is number-based, the records would be sorted in increment based on that column. If the first column is character-based, the list would be given with that column in alphabetical order. As an alternative, you can ask the database engine to include the records as they are made available, that is, the records of the first table, followed by those of the second table, and so on. To give this instruction, add the ALL keyword after UNION. The formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION ALL
SELECT WhatField(s) FROM AnotherTable;

Here is an example:

SELECT * FROM HumanResources.Employees
UNION ALL
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

Practical Learning: Uniting ALL Records

To unite all records, add the ALL keyword to the right of UNION:

USE LPM2;
GO
SELECT r.RegistrationNumber [Regist #],
       r.RegistrationDate   [Regist Date],
       r.TenantCode         [For Account #],
       r.PropertyNumber     [For Prop #],
       r.RentStartDate      [Rent Start On]
FROM Rentals.Registrations r
UNION ALL
SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
       a.ApartmentNumber, a.DateOccupiedFrom
FROM Rentals.Allocations a;
GO

Press F5 to execute
Click inside the Query Editor, press Ctrl + A, and press Delete

Copying From Many Tables

You can use the ability to copy records in order to get records from two or more tables and add them to a another table.

The formula to follow is:

INSERT INTO TableName
SELECT WhatField(s) FROM OneTable
UNION [ALL]
SELECT WhatField(s) FROM AnotherTable;

Here is an example:

USE LPM1;
GO

INSERT INTO Listing.Apartments
SELECT props.PropertyNumber,
       props.[Address],
       props.UnitNumber,
       props.City,
       props.[State],
       props.ZIPCode,
       props.Bedrooms,
       props.Bathrooms,
       props.MonthlyRate,
       props.SecurityDeposit
FROM Listing.Properties props
UNION
SELECT condos.CondoCode,
       condos.[Address],
       condos.CondoNbr,
       condos.City,
       condos.[State],
       condos.PostalCode,
       condos.Beds,
       condos.Baths,
       condos.Rent,
       condos.SecDepot
FROM Listing.Condominiums condos
UNION
SELECT alex.ApartmentCode,
       alex.[Address],
       alex.ApartNbr,
       alex.City,
       alex.[State],
       alex.ZIP,
       alex.Bedrooms,
       alex.Bathrooms,
       alex.MonthRent,
       alex.InitialDeposit
FROM Listing.AlexandriaApartments alex;
GO

Here is an example that displays the results

USE LPM1;
GO

SELECT aparts.PropertyNumber  N'Prop #',
       aparts.[Address],
       aparts.UnitNumber      N'Apart #',
       aparts.City,
       aparts.[State],
       aparts.ZIPCode         N'ZIP Code',
       aparts.Bedrooms        Beds,
       aparts.Bathrooms       Baths,
       aparts.MonthlyRate     Rent,
       aparts.SecurityDeposit Deposit
FROM Listing.Apartments aparts;
GO

ss F5

Practical Learning: Updating the Database

Type the following code:

USE LPM2;
GO
/* Change the property number where a record from Alexandria has
   the name property number as an apartment from Rockville */
UPDATE Listing.Properties
SET    PropertyNumber = N'3840-0002'
WHERE (PropertyNumber = N'3840-8262') AND (City = N'Alexandria');
GO
UPDATE Listing.Properties
SET    PropertyNumber = N'6200-0008'
WHERE (PropertyNumber = N'6200-8048') AND (City = N'Alexandria');
GO
UPDATE Listing.Properties
SET    PropertyNumber = N'3840-0003'
WHERE (PropertyNumber = N'3840-0683') AND (City = N'Alexandria');
GO
UPDATE Listing.Properties
SET    PropertyNumber = N'6286-0008'
WHERE (PropertyNumber = N'6286-6408') AND (City = N'Alexandria');
GO
-- Harmonize the security deposits based on the rate of each house
UPDATE Listing.Properties
SET    SecurityDeposit =  500 WHERE MonthlyRate >= 800;
GO
UPDATE Listing.Properties
SET    SecurityDeposit =  600 WHERE MonthlyRate >= 900;
GO
UPDATE Listing.Properties
SET    SecurityDeposit =  700 WHERE MonthlyRate >= 1000;
GO
UPDATE Listing.Properties
SET    SecurityDeposit =  800 WHERE MonthlyRate >= 1200;
GO
UPDATE Listing.Properties
SET    SecurityDeposit = 1000 WHERE MonthlyRate >= 1500;
GO
-- Add to the Rockville table of tenants the missing columns from the Alexandria table of customers
ALTER TABLE Rentals.Tenants
ADD DateCreated date;
GO
ALTER TABLE Rentals.Tenants
ADD DateCreated date;
GO
ALTER TABLE Rentals.Tenants
ADD EmergencyName nvarchar(40);
GO
ALTER TABLE Rentals.Tenants
ADD EmergencyPhone nvarchar(20);
GO
/* Change the tenants account numbers from Alexandria
   where they are the same as tenants in Rockville */
UPDATE Rentals.Customers
SET    AccountNumber = N'292000'
WHERE  AccountNumber = N'292475';
GO
UPDATE Rentals.Customers
SET    AccountNumber = N'293000'
WHERE  AccountNumber = N'293759';
GO
UPDATE Rentals.Customers
SET    AccountNumber = N'295000'
WHERE  AccountNumber = N'295804';
GO
UPDATE Rentals.Customers
SET    AccountNumber = N'524000'
WHERE  AccountNumber = N'524794';
GO
UPDATE Rentals.Customers
SET    AccountNumber = N'824000'
WHERE  AccountNumber = N'824857';
GO
-- Copy the Alexandria tenants to those in Rockville
INSERT INTO Rentals(TenantCode, DateCreated, FirstName, LastName,
                    MaritalStatus, NumberOfChildren, PhoneNumber,
					EmailAddress, EmergencyName, EmergencyPhone)
SELECT AccountNumber, DateCreated, FirstName, LastName, [Status],
       Children, Phone, Email, EmergencyName, EmergencyPhone
FROM Rentals.Customers;
GO
/* Change the records of the Alexandria registrations
   based on the properties numbers and the accounts numbers
   that were changed earlier */
UPDATE Rentals.Allocations
SET    ApartmentNumber = N'3840-0002', AccountNumber = N'295000'
WHERE  AllocationID = 2;
GO
UPDATE Rentals.Allocations
SET    ApartmentNumber = N'6200-0008', AccountNumber = N'824000'
WHERE  AllocationID = 5;
GO
UPDATE Rentals.Allocations
SET    ApartmentNumber = N'3840-0003', AccountNumber = N'292000'
WHERE  AllocationID = 7;
GO
UPDATE Rentals.Allocations
SET    ApartmentNumber = N'6286-0008', AccountNumber = N'293000'
WHERE  AllocationID = 9;
GO
-- Create a new Registrations table to use an identity column
CREATE TABLE Rentals.Agreements
(
	AgreementID      int identity(1, 1) not null,
	RegistrationDate Date,
	EmployeeNumber   int, -- Processed By
	TenantCode       nvarchar(10), -- Processed For
	PropertyNumber   nvarchar(10) not null,
	RentStartDate    date
);
GO
-- Add the Rockville regustrations to this table
INSERT INTO  Rentals.Agreements(RegistrationDate, EmployeeNumber, 
                                TenantCode, PropertyNumber, RentStartDate)
SELECT RegistrationDate, EmployeeNumber, TenantCode, PropertyNumber, RentStartDate
FROM Rentals.Registrations
GO
-- Then add the Alxandria registrations to the new table
INSERT INTO Rentals.Agreements(RegistrationDate, TenantCode, PropertyNumber, RentStartDate)
SELECT DateAllocated, AccountNumber, ApartmentNumber, DateOccupiedFrom)
FROM Rentals.Allocations;
GO
/* Change the registrations numbers of the Alexandria tenants based on
   the new identity numbers of the Agreements table */
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 11
WHERE  PaymentForAllocationNumber =  2;
GO
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 12
WHERE  PaymentForAllocationNumber =  5;
GO
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 13
WHERE  PaymentForAllocationNumber =  7;
GO
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 14
WHERE  PaymentForAllocationNumber =  9;
GO
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 15
WHERE  PaymentForAllocationNumber = 12;
GO
UPDATE Rentals.Receipts
SET    PaymentForAllocationNumber = 16
WHERE  PaymentForAllocationNumber = 15;
GO
-- Add the Alexandria payments to the Roclville's
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationNumber, AmountPaid, Notes)
SELECT PaymentAmt, PaymentForAllocationNumber, DateReceiptMade, ReceiptDescription
FROM Rentals.Receipts;
GO

To execute, on the main menu, click Query -> Execute
Close Microsoft SQL Server
When asked whether you want to save, click No