SQL JOINS examples 2

The Self-Join

A Self-Join (as the name suggests) is when a table is joined to itself and is made possible by aliasing the original table. The main reasons for using a Self-Join arise when you need to either compare the values in differ net rows in the same table.

EmployeeID	Name	Salary	ManagerID
023452	Ken Smith	45000	NULL
087652	Linda Jones	25000	023452

Employees Table

In order to get the name of the Linda's manager you would have to join the table to itself (using an alias to create a 'Manager' table). The join would occur ON Employees.ManagerID = Manager.EmployeeID. Thus we can construct the SQL statement using AS like so:

SELECT
Employees.EmployeeID, Employees.Name, Employees.Salary, Manager.Name AS Manager
FROM Employees 
LEFT JOIN Employees AS Manager
ON Employees.ManagerID = Manager.EmployeeID
WHERE (Employees.EmployeeID = '087652';

This would result in the following being output.

EmployeeID	Name	Salary	Manager
087652	Linda Jones	25000	Ken Smith

Aliasing Function output

Using AS with built in functions is discussed in more depth in Part 9 of this workshop series, but worth covering quickly here. Consider a quick query to find the average number of tracks.

mysql> SELECT AVG(tracks) 
    -> FROM cds;
+-------------+
| AVG(tracks) |
+-------------+
|     22.1667 |
+-------------+
1 row in set (0.00 sec)

AVG(tracks) does tell us certain information, but this field would be difficult to use as part of a script and therefore we can use AS to give a more meaningful name.

mysql> SELECT AVG(tracks) AS AverageTracks 
    -> FROM cds;
+---------------+
| AverageTracks |
+---------------+
|       22.1667 |
+---------------+
1 row in set (0.03 sec

UNION Joins

Union Joins allow the results of two queries to be combined into one outputted result set. This is done by having the 2 (or more) queries glued together by the UNION operator.

SELECT  
 FROM  
 WHERE 
UNION
SELECT  
 FROM WHERE 
For example if you wanted to search for artist beginning with either P or G you would construct two statements that searched each phrase and use the UNION statement.
mysql> SELECT artists.name 
    -> FROM artists 
    -> WHERE (artists.name LIKE 'P%') 
    -> UNION
    -> SELECT artists.name 
    -> FROM artists 
    -> WHERE (artists.name LIKE 'G%');
+-----------------+
| name            |
+-----------------+
| pop goes the 80 |
| Prince          |
| George Martin   |
+-----------------+
3 rows in set (0.03 sec)
By now with the knowledge you possess you will have worked out that this could just have easily been done by using two where conditions.
mysql> SELECT artists.name 
    -> FROM artists
    -> WHERE ((artists.name LIKE 'P%') || (artists.name LIKE 'G%'));
+-----------------+
| name            |
+-----------------+
| pop goes the 80 |
| Prince          |
| George Martin   |
+-----------------+
3 rows in set (0.00 sec)
However UNION also allows you to combine the results from different tables not just the same one. To give a practical, but somewhat unrealistic in a 'real world' sense lets return to our CDs database and select all the genres and all the labels that start with letters A to M.
mysql> SELECT label.name 
    -> FROM label 
    -> WHERE (label.name BETWEEN 'A%' AND 'M%')
    -> UNION
    -> SELECT genres.genre 
    -> FROM genres 
    -> WHERE (genres.genre BETWEEN 'A%' AND 'M%'); 
+----------------+
| name           |
+----------------+
| jive           |
| EMI            |
| Capitol        |
| Easy Listening |
| Heavy Metal    |
| Eighties       |
| Hip Hop        |
| Jazz           |
| Guitar Bands   |
+----------------+
9 rows in set (0.04 sec)
A more practical example would be to imagine if we had in fact made a separate producers table rather than just alias the artists table. We could query both tables using UNION to produce a result set.