Скачать в pdf «PostgreSQL»

Figure 6.4 shows the insertion of a row into the customer, employee, and part tables. It also shows the insertion of a row into the salesorder table, using the same customer, employee, and part numbers to link the salesorder row to the other rows we inserted. For simplicity, we will use only a single row per table.

6.4 Performing Joins

When data are spread across multiple tables, retrieval of that information becomes an important issue. Figure
6.5 indicates how to find the customer name for a given order number. It uses two queries. The first gets the customerjd for order number 14673. The returned customer identification number of 648 then is used in the where clause of the next query. That query finds the customer name record where the customerjd equals 648. We call this two-query approach a manual join, because the user manually took the result from the first query and placed that number into the where clause of the second query.

Fortunately, relational databases can perform this type of join automatically. Figure 6.6 shows the same join as in Figure 6.5 but places it in a single query. This query shows all of the elements necessary to perform the join of two tables:

•    The two tables involved in the join are specified in the from clause.

•    The two columns needed to perform the join are specified as equal in the where clause.

•    The salesorder table’s order number is tested in the where clause.

•    The customer table’s customer name is returned from the select.

Скачать в pdf «PostgreSQL»