Полезная информация

next up previous contents index
Next: Non-Equijoins Up: Joining Tables Previous: Unjoined tables

Self-Joins

By using table aliases described in section [*], you can refer to specific tables in the FROM clause using a shorter name. Figure [*] shows a rewrite of the query in figure [*] using aliases.

  
Figure: Using table aliases
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...ND
\par test->~~~~~~~~c.customer\_id~=~s.customer\_id\end{list}\par
\end{figure}

A c is used as an alias for the customer table, and s is used as an alias for the salesorder table.

You can even join a table to itself using table aliases. Such joins are called self-joins. The same table is given two different alias names. Each alias then represents a different version of the table. This may seem like a concept of questionable utility, but it can prove useful. Figure [*] shows practical examples.

  
Figure: Examples of self-joins using table aliases
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...art\_id~AND
\par test->~~~~~~~~c2.customer\_id~<>~648\end{list}\par
\end{figure}

The first query finds all customers in the same zipcode as customer number 648. The second finds all customers in the same zipcode as customer number 648, and then finds the order numbers placed by those customers. We have restricted the c2 table's customer id to not equal 648 because we don't want customer 648 to appear in the result. The third query goes farther by retrieving the part numbers associated with those orders.


next up previous contents index
Next: Non-Equijoins Up: Joining Tables Previous: Unjoined tables
Bruce Momjian
1999-11-21