Лабораторная работа №4. Соединение нескольких таблиц (продолжение)

Псевдоним (псевдоимя, alias) – временная (в пределах конкретного запроса) замена основного названия столбца или таблицы. Используются два варианта синтаксиса:

SELECT ИмяСтолбца AS ПсевдонимСтолбца или SELECT ИмяСтолбца ПсевдонимСтолбца

FROM Таблица FROM Таблица

аналогично

SELECT Список столбцов или SELECT Список столбцов

FROM Таблица AS ПсевдонимТаблицы FROM Таблица ПсевдонимТаблицы

Псевдонимы можно использовать для ссылки на столбец в ORDER BY и для лучшего именования столбцов. Запрещено ссылаться на псевдоним в определении WHERE. Вызвано это тем, что при выполнении кода WHERE значение столбца может быть еще не определенным.

Пример 1.

SELECT LastName AS Family, Title AS MyField

FROM Person.Contact

ORDER BY MyField

Пример 2.

SELECT COUNT (LastName) SUMM, Title

FROM Person.Contact

GROUP BY Title

Пример 3.

SELECT V.Name, P.Name

FROM

Purchasing.Vendor V

JOIN

Purchasing.ProductVendor PV

ON V.VendorID=PV.VendorID

JOIN

Production.Product P

ON P.ProductID=PV.ProductID

Не обязательно использовать каждый псевдоним или таблицу, которые упомянуты в предложении FROM, в предложении SELECT. Иногда, они запрашиваются исключительно потому, что могут вызываться в предикате запроса (WHERE).

Пример 4.

SELECT b.ProductID, b.LocationID

FROM

Production.ProductInventory b

JOIN

Production.Location a

ON b.LocationID = a.LocationID

WHERE a.Name LIKE 'Paint%'

 

Самообъединение (рекурсивное соединение ) – объединение таблицы с самой собой на основе операции эквисоединения. Соединения создаются в результате включения в запрос дубликата таблицы с последующим объединением между полями копий (в этом случае сравниваются значения внутри столбца одной таблицы).

Пример 5 – поиск пар руководитель-подчиненный (в списке выбора используется текстовая константа)

SELECT Boss.EmployeeID, 'boss for', Employee.EmployeeID

FROM

HumanResources.Employee AS Boss

JOIN

HumanResources.Employee AS Employee

ON Boss.EmployeeID=Employee.ManagerID

ORDER BY Boss.ManagerID

Пример 6 – поиск всех пар заказчиков, имеющих одинаковый рейтинг

SELECT first.Name, second.Name, first.CreditRating

FROM

Purchasing.Vendor first

JOIN

Purchasing.Vendor second

ON

first.CreditRating = second.CreditRating

WHERE first.Name < second.Name Без последней строки значение Name в псевдониме first сначала выбирается в комбинации со значением Name в псевдониме second, а затем значение Name в псевдониме second выбирается в комбинации со значением Name в псевдониме first. Чтобы этого избежать, нужно налагать порядок на два значения, так чтобы одно было меньше другого или предшествовало ему в алфавитном порядке.

Это свойство SQL можно использовать для проверки определенных видов ошибок:

Пример 7.

SELECT C1.FirstName, C1.MiddleName, C1.LastName, C1.EmailAddress, C1.Phone, C1.ContactID, C2.ContactID

FROM

Person.Contact C1

JOIN

Person.Contact C2

ON

C1.FirstName=C2.FirstName AND

C1.MiddleName=C2.MiddleName AND

C1.LastName=C2.LastName AND

C1.EmailAddress=C2.EmailAddress AND

C1.Phone=C2.Phone

WHERE C1.ContactID <> C2.ContactID

Для одной таблицы в запросе можно использовать любое число псевдонимов.

Можно создать соединение, которое включает и различные таблицы, и псевдонимы одной таблицы.

Пример 8.

SELECT A.ContactID, B.ContactID, A.TerritoryID, Sales.SalesTerritory.CountryRegionCode

FROM

Sales.SalesOrderHeader A

JOIN

Sales.SalesOrderHeader B

ON A.TerritoryID = B.TerritoryID

JOIN

Sales.SalesTerritory

ON Sales.SalesTerritory.TerritoryID=A.TerritoryID

WHERE A.ContactID > B.ContactID UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов. UNION отличается от соединений тем, что он не основан на декартовом произведении. Все запросы выполняются независимо друг от друга, а уже вывод их объединяется.

Пример 9.

SELECT SalesOrderID, ContactID, CustomerTypeFROM Sales.SalesOrderHeader JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID=Sales.Customer.CustomerIDWHERE CustomerType='I'UNION SELECT SalesOrderID, ContactID, CustomerType FROM Sales.SalesOrderHeader JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID=Sales.Customer.CustomerIDWHERE CustomerType='S' Требования совместимости: к огда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов и в том же порядке что и остальные, и тип столбцов должен быть совместимый с типами других столбцов, занимающих такую же позицию в списке выбора. Следующий запрос выполняться не будет, так как СУБД не может привести строковую константу к типу integer

Пример10.

SELECT A.AddressID, 'Dallas', A.CustomerID

FROM

Sales.CustomerAddress A

JOIN

Person.Address B

ON A.AddressID=B.AddressID

WHERE B.City='Dallas'

UNION

SELECT C.VendorID, C.AddressID, 'Los Angeles'

FROM

Purchasing.VendorAddress C

JOIN

Person.Address B

ON C.AddressID=B.AddressID

WHERE B.City='Los Angeles'По умолчанию UNION исключает дубликаты строк из вывода. Если хочется вывести все строки, нужно использовать ключевое слово ALL. Пример 11. SELECT CustomerID, CustomerType FROM Sales.Customer WHERE CustomerType='I'UNION ALLSELECT CustomerID, CustomerType FROM Sales.CustomerWHERE TerritoryID=1 Оператор UNION можно использовать в качестве разновидности оператора IF для отображения различных значений для одного поля в зависимости от значений в других полях. Пример 12.

SELECT SalesOrderID, '20% off', LineTotal, LineTotal* 0.80

FROM Sales.SalesOrderDetail

WHERE LineTotal < 1000

UNION

SELECT SalesOrderID, '30% off', LineTotal, LineTotal* 0.70

FROM Sales.SalesOrderDetail

WHERE LineTotal < 10000

UNION

SELECT SalesOrderID, '10% off', LineTotal, LineTotal* 0.90

FROM Sales.SalesOrderDetail

WHERE LineTotal < 500

Использование текстовых констант в списках выбора для каждого запроса позволяет маркировать результаты в едином наборе строк по принадлежности к той или иной части результирующего запроса.