10. Joins Revisited

Tags

Top Highlights

• If you are lazy (and aren’t we all), you can choose a join type that allows you to name the tables to be joined but lets the database server determine what the join conditions need to be. Known as the natural join, this join type relies on identical column names across multiple tables to infer the proper join conditions. For example, the rental table includes a column named customer_id, which is the foreign key to the customer table, whose primary key is also named customer_id

• mysql> SELECT ones.num + tens.num + hundreds.num -> FROM -> (SELECT 0 num UNION ALL -> SELECT 1 num UNION ALL -> SELECT 2 num UNION ALL -> SELECT 3 num UNION ALL -> SELECT 4 num UNION ALL -> SELECT 5 num UNION ALL -> SELECT 6 num UNION ALL -> SELECT 7 num UNION ALL -> SELECT 8 num UNION ALL -> SELECT 9 num) ones -> CROSS JOIN -> (SELECT 0 num UNION ALL -> SELECT 10 num UNION ALL -> SELECT 20 num UNION ALL -> SELECT 30 num UNION ALL -> SELECT 40 num UNION ALL -> SELECT 50 num UNION ALL -> SELECT 60 num UNION ALL -> SELECT 70 num UNION ALL -> SELECT 80 num UNION ALL -> SELECT 90 num) tens -> CROSS JOIN -> (SELECT 0 num UNION ALL -> SELECT 100 num UNION ALL -> SELECT 200 num UNION ALL -> SELECT 300 num) hundreds;

• The join definition was changed from inner to left outer, which instructs the server to include all rows from the table on the left side of the join (film, in this case) and then include columns from the table on the right side of the join (inventory) if the join is successful. The num_copies column definition was changed from count(*) to count(i.inventory_id), which will count the number of non-null values of the inventory.inventory_id column.

• While you may have expected 1,000 rows to be returned (one for each film), the query returns only 958 rows. This is because the query uses an inner join, which only returns rows that satisfy the join condition. The film Alice Fantasia (film_id 14) doesn’t appear in the results, for example, because it doesn’t have any rows in the inventory table.

• If you want the query to return all 1,000 films, regardless of whether or not there are rows in the inventory table, you can use an outer join, which essentially makes the join condition optional

• The results are the same for Ali Forever and Alien Center, but there’s one new row for Alice Fantasia, with a null value for the inventory.inventory_id column. This example illustrates how an outer join will add column values without restricting the number of rows returned by the query. If the join condition fails (as in the case of Alice Fantasia), any columns retrieved from the outer-joined table will be null.

• The keyword left indicates that the table on the left side of the join is responsible for determining the number of rows in the result set, whereas the table on the right side is used to provide column values whenever a match is found.

• Since you will rarely (if ever) encounter right outer joins, and since not all database servers support them, I recommend that you always use left outer joins. The outer keyword is optional, so you may opt for A left join B instead, but I recommend including outer for the sake of clarity.

• Cross Joins

• the concept of a Cartesian product, which is essentially the result of joining multiple tables without specifying any join conditions.

• a situation in which I find the cross join to be quite helpful.

• how to use subqueries to fabricate tables. The example I used showed how to build a three-row table that could be joined to other tables.

• mysql> SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit     -> UNION ALL     -> SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit     -> UNION ALL     -> SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit; +---------------+-----------+------------+

• While this table was exactly what was needed for placing customers into three groups based on their total film payments, this strategy of merging single-row tables using the set operator union all doesn’t work very well if you need to fabricate a large table.

• If you take the Cartesian product of the three sets {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {0, 10, 20, 30, 40, 50, 60, 70, 80, 90}, and {0, 100, 200, 300} and add the values in the three columns, you get a 400-row result set containing all numbers between 0 and 399.

• SELECT DATE_ADD('2020-01-01',     ->   INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt

• Natural Joins

• Because you specified a natural join, the server inspected the table definitions and added the join condition r.customer_id = c.customer_id to join the two tables. This would have worked fine, but in the Sakila schema all of the tables include the column last_update to show when each row was last modified, so the server is also adding the join condition r.last_update = c.last_update, which causes the query to return no data.

• The only way around this issue is to use a subquery to restrict the columns for at least one of the tables

• Absolutely not; you should avoid this join type and use inner joins with explicit join conditions.