Explicit join operations page 2
Find all PC models, makers, and prices:
It is essential that using the WHERE clause is indispensable for sampling only PC makers as compared with the previous example. Otherwise, laptop and printer models will also get into the result set. This condition is negligible for the above example because the rows only were joined with identical model numbers, and the PC table was between the two joined tables. Here is the result set:
Because models 2111 and 2112 in the Product table are absent in the PC table, the columns of the PC table contain NULL.
The RIGHT JOIN is the reverse of the LEFT JOIN, i.e. the result set will include all the rows from the second table and only those rows from the first table for which the join condition is met. In our case the left join
Just the query
Finally, the result set with FULL JOIN will include not only the matching rows in the both tables but also all conceivable non-matching rows in initial tables. All the non-matching column values in both tables are returned as NULL values. A full join presents the combination of the left and right outer joins.
So, for the above mentioned A and B tables, the query
Note this join is symmetrical, i.e. "A FULL JOIN B" is equivalent to "B FULL JOIN A". Also, it should be noted that notation A.* implies "all columns from the А table".
Suggested exercises: 16, 29, 30, 34, 46, 55, 56, 59, 60, 64, 69, 70, 74, 109, 113, 128