02:09

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise #7 page 3

It is possible to use join instead of IN predicate, however SQL Server gives for these two cases identical execution plans

Console

Execute

SELECT a.model, price 
FROM (SELECT model, price 
 FROM PC 
 UNION
 SELECT model, price 
  FROM Laptop
 UNION
 SELECT model, price 
 FROM Printer
 ) AS a JOIN 
 Product p ON a.model = p.model
WHERE p.maker = 'B';

The alternative to the queries, that use the union, may be the queries implying join. For this problem such a solution will have less efficient execution plan, although in other cases it can prove to be preferable. In any case, for educative purposes it will be useful to consider different ways of solution of the problem, we suggest you to do it on your own.

T&S

To solve the problem on SQL-EX.RU

Pages

UNION exercise 7

Previos [Exercise #6]

[Exercise #8] Next

Last added:

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT EXCEPT exercise (-2) exercise 19 exercise 23 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY grouping Guadalcanal More tags

The book was updated
yesterday

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise #7 page 3