How to find out the models and prices for all the products (of any type) produced by maker B The database scheme consists of four tables:


Product (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
Laptop (code, model, speed, ram, hd, screen, price)
Printer (code, model, color, type, price)


The Product table contains data on the maker, model number, and type of product (PC, Laptop, or Printer). It is assumed that model numbers in the Product table are unique for all makers and product types.

Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed column, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, 4x) - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.

For each printer model in the Printer table, its output type (y for color and n for monochrome) – color column, printing technology (Laser, Jet, or Matrix) – type, and price are specified.

Now the query I came up with:

SELECT PC.model, price FROM PC 
INNER JOIN Product ON PC.model = Product.model 
UNION
SELECT Laptop.model, price FROM Laptop 
INNER JOIN Product ON Laptop.model = Product.model 
UNION
SELECT printer.model, price FROM printer 
INNER JOIN Product ON printer.model = Product.model 
WHERE Product.maker='B'


The result should be:

model   price
1121    850.0000
1750    1200.0000


I am doing SQL exercise and not able to get the same result.
In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (This is unlike e.g. ORDER BY, which you would be allowed to specify only once and it would apply to the combined set.)

So, if you want products by the same maker B from each of the three category tables, you need to repeat the filter for each sub-select:

SELECT PC.model, price FROM PC 
INNER JOIN Product ON PC.model = Product.model 
WHERE Product.maker='B'
UNION
SELECT Laptop.model, price FROM Laptop 
INNER JOIN Product ON Laptop.model = Product.model 
WHERE Product.maker='B'
UNION
SELECT printer.model, price FROM printer 
INNER JOIN Product ON printer.model = Product.model 
WHERE Product.maker='B'


That may look like redundant coding, which it probably is, and there are ways to eliminate the redundancy in this case – for instance by using a derived table:

SELECT
  derived.model, derived.price
FROM
  (
    SELECT model, price FROM PC
    UNION
    SELECT model, price FROM Laptop
    UNION
    SELECT model, price FROM printer
  ) AS derived
  INNER JOIN Product ON derived.model = Product.model 
WHERE
  Product.maker='B'
;


But in SQL, the DRY principle does not apply as often as in other languages, meaning that repetition is not always bad in SQL and eliminating it is not always good. In theory, the two queries above should be equivalent. In practice, however, not every query optimiser might recognise that, and the second query might result in a less efficient execution plan.

In short, repeating a condition several times across a single query is not uncommon in SQL. Sometimes repetition becomes too much and, to help maintainability of your queries, you may want to employ views or stored routines. But in such cases, too, revisiting your schema often has an even better impact.