05:13

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Using multiple tables in a query

Explicit join operations page 2

Example 5.6.4

Find all PC models, makers, and prices:

Console

Execute

SELECT maker, Product.model AS model_1, pc.model AS model_2, price
FROM Product LEFT JOIN 
 PC ON PC.model = Product.model
WHERE type = 'pc'
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

Product LEFT JOIN PC ON PC.model = Product.model

will be equivalent to the following right join

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

Just the query

Console

Execute

SELECT maker, Product.model AS model_1, PC.model AS model_2, price
FROM Product RIGHT JOIN 
 PC ON PC.model = Product.model
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

SELECT A.*, B.*
FROM A FULL JOIN 
  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".

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

Pages

LEFT OUTER JOIN RIGHT JOIN FULL JOIN outer joins

Previous [Using multiple tables in a query]

[Equi-joins] Next

Last added:

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

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Using multiple tables in a query

Explicit join operations page 2