loading..
Русский    English
02:14

Explicit join operations page 2

Example 5.6.4

Find all PC models, makers, and prices:

Console
Execute
  1. SELECT maker, Product.model AS model_1, pc.model AS model_2, price
  2. FROM Product LEFT JOIN
  3. PC ON PC.model = Product.model
  4. WHERE type = 'pc'
  5. ORDER BY maker, PC.model;

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:

Maker model_1 model_2 price
A 1232 1232 600
A 1232 1232 400
A 1232 1232 350
A 1232 1232 350
A 1233 1233 600
A 1233 1233 950
A 1233 1233 980
B 1121 1121 850
B 1121 1121 850
B 1121 1121 850
E 2111 NULL NULL
E 2112 NULL NULL
E 1260 1260 350

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

  1. Product LEFT JOIN PC ON PC.model = Product.model
will be equivalent to the following right join

  1. PC RIGHT JOIN Product ON PC.model = Product.model

Just the query

Console
Execute
  1. SELECT maker, Product.model AS model_1, PC.model AS model_2, price
  2. FROM Product RIGHT JOIN
  3. PC ON PC.model = Product.model
  4. ORDER BY maker, PC.model;
gives the same results as those for inner join, because the right table (PC) has no models that would be missing from the left table (Product), which is reasonable for one-to-many relationship type that occurs between the PC and Product tables.

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

  1. SELECT A.*, B.*
  2. FROM A FULL JOIN
  3.   B ON A.a = B.c;
gives the following result set:

A b C d
1 2 NULL NULL
2 1 2 4
NULL NULL 3 3

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". 

Next page

Suggested exercises: 16, 29, 30, 34, 46, 55, 56, 59, 60, 64, 69, 70, 74, 109, 113, 128

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
https://exchangesumo.com/obmen/WMZ-WMR/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100