Join Operation in SQL

The NATURAL JOIN uses all columns with the same name in the two source tables for an implicit equi-join (a JOIN in which the values in those columns are equal), while the column name JO I Nallows you to specify a US I NGclause so that you can further restrict the columns used to a subset of those columns having the same name. By contrast, the condition JOIN lets you specify an arbitrary search condition to determine how the rows of the two tables will be joined. Sometimes, the ON clause is redundant with the WHERE clause; however, we like to use the ONclause to specify conditions specifically related to the JOIN and use the WHERE clause for additional filtering of the result of the JOIN. ON and WHERE are not interchangeable, though; our discussion of OUTERJOINs will make that clear shortly. 8.3.7 The INNER JOIN The types of O01N operations we've discussed thus far are known in the relational model and in SQL:1999 terminology as inner joins. For the sake of clarity, you 8.3 Types of Join Operations 243 can explicitly specify that a particular statement represents an INNER JOIN, as shown in Example 8-14. Example8-14 INNER JOIN Syntax SELECT * FROM tl INNER JOIN t2 USING (cl, c2) ; The use of INNERh a s no additional effects, but it does help your statement to be completely self-documenting. 8.3.8 The OUTER JOIN As opposed to the INNER JOIN, the OUTER JOIN operation preserves unmatched rows from one or both tables, depending on the keyword used: LEFT, RIGHT, or FULL. That is, an INNERJOIN disregards any rows where the specific search condition isn't met, while an OUTER JOIN maintains some or all of the unmatched data. Let's look at some examples. LEFT OUTER JOIN The LEFT OUTER JOIN preserves unmatched rows from the left table, the one that precedes the keyword JO I N. Let's look at our condition JO I Nexample earlier from Example 8-11, where we use values in differently named columns as our match- ing criteria.

Retrieving Data from More Than One Table: Joins As you read in Chapter 3, logical relationships between entities in a relational database are represented by matching primary and for- eign key values. Given that there are no permanent connections be- tween tables stored in the database, a DBMS must provide some way for users to match primary and foreign key values when need- ed. This capability is provided by the relational algebra join opera- tion, which combines two tables based on a specified relationship between data they contain. In this chapter you will first learn about how joins work. Then you will be introduced to the syntax for including a join in a SQL query. Throughout this chapter you will read about the impact joins have on database performance. At the end you will see how subqueries (SELECTS within SELECTS) can be used to avoid joins, and in some 65 66 RETRIEVING DATA FROM MORE THAN ONE TABLE: JOINS cases, significantly decrease the time it takes for a DBMS to com- plete a query. The Relational Algebra Join Operation The relational algebra join creates one result table from two source tables by pasting a row from one source table onto the end of a row from the other source table. A DBMS uses a relationship between data in the two source tables to determine which rows should be combined. A Non-Database Example To help you understand how a join works, we will begin with an ex- ample that has absolutely nothing to do with relations. Assume that you have been given the task of creating manufacturing part assem- blies by connecting two individual parts. The parts are classified as either A parts or B parts. There are many types of A parts (A1 through An, where n is the total number of types of A parts) and many types of B parts (B1 through Bn). Each B is to be matched to the A part with the same number; conversely, an A part is to be matched to a B part with the same number.

An outer join is a join returning an intersection plus rows from either or both tables, not in the other table. 6.4 Joins 205 Chapter 6 6.4.1 Efficient joins What is an efficient join? An efficient join is a join SQL query that can be tuned to an acceptable level of performance. Certain types of join queries are inherently easily tuned and can give good performance. In general a join is efficient when it can use indexes on large tables, or is reading only very small tables. Moreover any type of join will be inefficient if coded improperly. 6.4.1.1 Intersections An inner or natural join is an intersection between two tables. In mathe-matical set parlance an intersection contains all elements occurring in both of the sets (elements common to both sets). An intersection is efficient when index columns are matched together in join clauses. Intersection matching not using indexed columns will be inefficient. In that case you may want to create alternate indexes. On the other hand, when a table is very small the optimizer may conclude that reading the whole table is faster than reading an associated index, plus the table. In the example below both of the two tables are so small that the opti-mizer does not bother with the indexes, simply reading both of the tables fully: SELECT r.region, c.country FROM region r JOIN country c USING(region_id) When joining a very small with a very large table, it is likely the small table would be read in full and the large table with an index: SELECT b.branch_name, a.account_no FROM branch b JOIN accounts USING (branch_no) The most efficient type of inner join will generally be one retrieving very specific rows, such as in the next example. Most SQL is more efficient when retrieving very specific, small numbers of rows: SELECT r.region, c.country, b.branch_name, a.account_no FROM region r JOIN country c USING (region_id) JOIN branch b USING (country_id) JOIN accounts a USING (branch_no) WHERE a.account_no = 100

This operation, very often used in practice, allows for combining relations with a semantic link conveyed by the presence (in their schema) of one of several attributes sharing the same domains. The principle of the join is illustrated by Figure 2.7. In practice, the comparison operator is often equality, and it Fig. 2.7 Principle of the join operation 16 Fuzzy Preference Queries to Relational Databases is possible to define a variant called equijoin in the following way: equijoin ( r, s, A, B ) = { u, v | u ∈ r and v ∈ s and ( u.A = v.B ) and v = v. ( Y − B ) } , or: equijoin ( r, s, A, B ) = project (( join ( r, s, A, B, =) , X ∪ Y − B ) , where X ∩ Y = ∅ . A side-effect is to avoid generating useless (duplicate) information, since in every tuple of the output relation the B -value would be equal to the A -value (with the previous definition). The interest of the equijoin is shown in the next example. Example 2.4. The two relations of Example 2.3 have a semantic link due to the presence of the compatible attributes work-serv (in emp ) and s-id (in ser ). The query to look for any pair ( e-name, s-name ) such that e-name works in s-name and s-name corresponds to a service which has a budget less than $15M can be written thanks to an equijoin, as follows: project ( equijoin ( emp, select ( ser, budget < 15) , { work-serv } , { s-id } ) , { e-name, s-name } ) . With the extensions given in Tables 2.2 and 2.3, services numbered 1 and 10 fulfill the budget requirement, and the final result is the binary relation of Table 2.4. Table 2.4 Result of Example 2.4 e-name s-name Dupond manufacturing Mesnard manufacturing Perrin marketing Several other specific forms of join exist, among which are: • self-join, where the same relation is used twice, which enforces the renam-ing of the attributes in the resulting relation, • natural join, which corresponds to the equijoin of two relations over the set of attributes having the same name in both. With the schemas