Database Concepts - Data Warehouse Systems: Design and Implementation - page 35

SELECT DISTINCT Country
FROM Customers
This query returns the set of countries of the Northwind customers, without
duplicates. If the DISTINCT keyword is removed from the above query, then
it would return as many results as the number of customers in the database.
As another example, the query “Identifier, first name, and last name of the
employees hired between 1992 and 1994,” which we presented when discussing
the projection and selection operations, reads in SQL:
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate > = ' 1992-01-01 ' and HireDate < = ' 1994-12-31 '
The binary operations of the relational algebra are supported in SQL:
union, intersection, difference, and the different kinds of joins. Recall the
query “Identifiers of employees from the UK, or who are reported by an
employee from the UK.” In SQL, it would read:
SELECT EmployeeID
FROM Employees
WHERE Country= ' UK '
UNION
SELECT ReportsTo
FROM Employees
WHERE Country= ' UK '
Notice that the UNION in the above query removes duplicates in the result,
whereas the UNION ALL will keep them, that is, if an employee is from the
UK and is reported by at least one employee from the UK, it will appear
twice in the result.
The join operation can be, of course, implemented as a projection of a
selection over the Cartesian product of the relations involved. However, in
general, it is easier and more ecient to use the join operation. For example,
the query “Name of the products supplied by suppliers from Brazil” can be
written as follows:
SELECT ProductName
FROM Products P, Suppliers S
WHERE P.SupplierID = S.SupplierID AND Country = ' Brazil '
An alternative formulation of this query is as follows:
SELECT ProductName
FROM Products P JOIN Suppliers S ON P.SupplierID = S.SupplierID
WHERE Country = ' Brazil '
On the other hand, the outer join operations must be explicitly stated in
the FROM clause. For example, the query “First name and last name of
employees, together with the first name and last name of their supervisor,