Aggregate Functions

So far, we’ve been looking at queries that retrieve data from single table at a time. Single table queries are useful but they do not exploit the full power of the SQL language. SQL is relational database query language and as such, one of it’s most important features is it’s ability to retrieve information from several different related tables. In relational database terms, this process is called a join. The tables to be joined are named in the FORM clause of the SELECT with each table name separated by a comma. The relationships between the tables in a join are defined by the predicate in the WHERE clause. The predicate can refer to any column from the joined tables to form the relations.

Example

SELECT EmployeeAddressTable.EmployeeIDNo, 
EmployeeAddressTable.FirstName, 
EmployeeAddressTable.LastName, 
EmployeeStatisticsTable.Salary 
FROM EmployeeAddressTable, EmployeeStatisticsTable 
WHERE EmployeeAddressTable.EmployeeIDNo = EmployeeStatisticsTable.EmployeeIDNo 

The preceding example retrieves id, first name, last name and salary of employee. Salary field comes from joined table. This SQL statement would return all rows from the EmployeeAddressTable and EmployeeStatisticsTable tables where there is a matching EmployeeIDNo value in both the EmployeeAddressTable and EmployeeStatisticsTable tables.

Inner Join

It is, in fact, the most common type of join. Inner joins return all rows from multiple tables where the join condition is met. There must be a matching value in a field common to both tables. An INNER JOIN cannot be nested inside a LEFT JOIN or RIGHT JOIN.

SELECT EmployeeAddressTable.EmployeeIDNo, 
EmployeeAddressTable.FirstName, 
EmployeeAddressTable.LastName, 
EmployeeStatisticsTable.Salary 
FROM EmployeeAddressTable INNER JOIN EmployeeStatisticsTable 
ON EmployeeAddressTable.EmployeeIDNo = EmployeeStatisticsTable.EmployeeIDNo 

With the INNER JOIN operator, any relational comparison operator can be used in the ON clause: =, <, >, <=, >=, or <>. The above example returns all cases where the value in the ‘EmployeeID’ field of the ‘Employee’ table matches the ‘EmployeeID’ field of the ‘EmployeeStatisticsTable’ table. If there are rows in EmployeeAddressTable that do not have matches in EmployeeStatisticsTable, those rows will not be listed.

Left Join

The LEFT JOIN operator is used to create a left outer join and includes all of the records from the first (left) of the two tables, even if there are no matching values for records in the second.

SELECT EmployeeAddressTable.EmployeeIDNo, 
EmployeeAddressTable.FirstName, 
EmployeeAddressTable.LastName, 
EmployeeStatisticsTable.Salary 
FROM EmployeeStatisticsTable LEFT JOIN EmployeeAddressTable 
ON EmployeeAddressTable.EmployeeIDNo = EmployeeStatisticsTable.EmployeeIDNo 

This SQL statement returns all the rows from the first table (EmployeeStatisticsTable), even if there are no matches in the second table (EmployeeAddressTable). If there are rows in EmployeeStatisticsTable that do not have matches in EmployeeAddressTable, those rows also will be listed. In the result set they will be displayed as .

Right Join

The RIGHT JOIN operator is used to create a right outer join and includes all of the records from the second (right) of the two tables, even if there are no matching values for records in the first.

SELECT EmployeeAddressTable.EmployeeIDNo, 
EmployeeAddressTable.FirstName, 
EmployeeAddressTable.LastName, 
EmployeeStatisticsTable.Salary 
FROM EmployeeStatisticsTable left join EmployeeAddressTable 
ON EmployeeAddressTable.EmployeeIDNo = EmployeeStatisticsTable.EmployeeIDNo 

This example of a RIGHT JOIN returns all the rows from the second table (EmployeeAddressTable), even if there are no matches in the first table (EmployeeStatisticsTable). If there had been any rows in EmployeeAddressTable that did not have matches in EmployeeStatisticsTable, those rows also would have been listed.