8.4 Working with Aliases and Self-Joins

Aliases are supported by almost every sophisticated database. Aliases are not only comfortable for writing shorter queries; they can also be used to build more complex queries where one table is treated as a set of tables.

8.4.1 Using Aliases

Before you learn about self-joins, it is time to have a closer look at aliases. An alias is an alternative name for an object.

Take a look at an example:

 phpbook=# SELECT a.* FROM stock AS a LIMIT 1;    name   |    pday    | price ----------+------------+-------  Cybertec | 2002-06-12 | 23.40 (1 row) 

a is an alias for stock and can be used just like the original name. To define an alias, the keyword AS and the name of the alias must be added to the FROM clause. In this example you have seen how to define an alias for a table. If you want to define an alias for a column, you will find out that it is not possible:

 phpbook=# SELECT pday AS date_of_price FROM stock WHERE date_of_price='2002/06/12'; ERROR:  Attribute 'date_of_price' not found 

In some cases it can be uncomfortable that aliases cannot be used in WHERE clauses especially when working with aggregated information:

 phpbook=# SELECT COUNT(*) FROM stock HAVING COUNT(*) > 2;  count -------      6 (1 row) 

As you can see, we have added the COUNT function to the HAVING clause as well. Let's add an alias for the column:

 phpbook=# SELECT COUNT(*) AS a FROM stock HAVING COUNT(*) > 2;  a ---  6 (1 row) 

The new headline of the column is a instead of count now. Let's try to use the alias in the HAVING clause:

 phpbook=# SELECT COUNT(*) AS a FROM stock HAVING a > 2; ERROR:  Attribute 'a' not found 

PostgreSQL 7.2 will display an error because the alias is not a valid attribute in the HAVING clause. We strongly recommend that you keep this syntax in mind because otherwise you might have trouble writing complex queries that use HAVING clauses.

8.4.2 Self-Joins Built on Aliases

One of the most important situations when you need to work with aliases is when performing a self-join. Performing a self-join means that a table is being joined with itself. To show you in a practical way what this means, you can take a look at the next example.

Imagine a table storing a list of products and the category these products belong to:

 phpbook=# CREATE TABLE product_cat(name text, category text); CREATE 

The table consists of two columns. The first column contains the name of the product and the second column contains the category of the product. Let's insert some values into the table:

 phpbook=# INSERT INTO product_cat VALUES('PostgreSQL Developer''s Handbook', 'literature'); INSERT 25362 1 phpbook=# INSERT INTO product_cat VALUES('PostgreSQL Developer''s Handbook', 'book'); INSERT 25363 1 phpbook=# INSERT INTO product_cat VALUES('PostgreSQL Developer''s Handbook', 'computer'); INSERT 25364 1 phpbook=# INSERT INTO product_cat VALUES('Sendmail for Linux', 'book'); INSERT 25365 1 phpbook=# INSERT INTO product_cat VALUES('1984', 'book'); INSERT 25366 1 phpbook=# INSERT INTO product_cat VALUES('1984', 'literature'); INSERT 25367 1 

Six records have been added to the table. In the PostgreSQL Developer's Handbook, the single quote had to be escaped:

 phpbook=# SELECT * FROM product_cat;               name               |  category ---------------------------------+------------  PostgreSQL Developer's Handbook | literature  PostgreSQL Developer's Handbook | book  PostgreSQL Developer's Handbook | computer  Sendmail for Linux              | book  1984                            | book  1984                            | literature (6 rows) 

The goal of the next example is to write a query that retrieves all products that belong to the categories literature and book. The problem is that this information cannot be retrieved with things you have learned so far because attributes in different records must be combined. Things would be much easier if the list of categories a product belongs to could be listed as separate columns, but this would be far too inflexible.

To get around the problem, you can use aliases. One table will be assigned to two aliases, so this table will appear as two independent tables that can be joined. Take a look at a piece of code:

 SELECT a.*         FROM product_cat AS a, product_cat AS b         WHERE a.name=b.name                 AND a.category='literature'                 AND b.category='book'; 

The query will retrieve all records that belong to the categories literature and book. If you take a closer look at the query, you can see how things work: Two aliases are being created, so the tables appear just as if they were two independent copies of the same table. These two tables can easily be joined and the category of the first table is set to literature, whereas the category selected from the second table is set to book. This way it is an easy task to retrieve records that belong to exactly two categories.

In this example two records will be returned:

               name               |  category ---------------------------------+------------  PostgreSQL Developer's Handbook | literature  1984                            | literature (2 rows) 

The same result can also be included by using just one alias. In this book we have included both versions so that you can see better how to do this. Some books about SQL use the version with two aliases and others show examples like that with only one alias:

 SELECT a.*         FROM product_cat AS a, product_cat         WHERE a.name=product_cat.name                 AND a.category='literature'                 AND product_cat.category='book'; 

Self-joins can also be performed to retrieve records that belong to three or more categories. In this case the SQL statement will be more complex because three tables must be joined, as shown in the next listing:

 SELECT a.name         FROM product_cat AS a,                 product_cat AS b,                 product_cat AS c         WHERE a.name=b.name                 AND b.name=c.name                 AND a.category='literature'                 AND b.category='book'                 AND c.category='computer'; 

There is only one record in the table belonging to all three categories listed in the query:

               name ---------------------------------  PostgreSQL Developer's Handbook (1 row) 

As you can see, self-joins are a powerful method for performing complex queries where conditions affect more than just one line.