1

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.

5

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.

  • Minor nitpick, in later versions of SQL you can actually order by in this way. I'm not positive when this was introduced but it exists for sure in SQL2008. It is typically used together with fetch first n rows only for optimization purposes. Example: (select x from A order by x fetch first 10 rows only) union (select y from B order by y fetch first 10 rows only). I tested this statement against DB2 10.5 and PostgreSQL 9.3 – Lennart Apr 4 '16 at 18:30
  • In my experience your second query will often perform worse than the first query (as you correctly points out). One trick that can help there is using a LATERAL join. Something like ` SELECT derived.model, derived.price from Product JOIN LATERAL ( SELECT model, price FROM PC where PC.model = Product.model union .... ) ON derived.model = Product.model WHERE ...`. I agree with most of what you say in your answer and I consider it to be a a good one, so my comments are just minor reflections. – Lennart Apr 4 '16 at 18:42
  • @Lennart Thanks. Didn't know about the ORDER BY ... FETCH thing, will try and play with it in SQL Server tomorrow morning. – Andriy M Apr 4 '16 at 20:29
  • According to: blog.jooq.org/2015/02/03/… (slide 111), SQL Server supports fetch first ... from version 2012. LATERAL is not directly supported in SQL Server, but you can use CROSS APPLY instead. – Lennart Apr 4 '16 at 20:32
  • @Lennart: Yes, SQL Server supports FETCH FIRST, that I already knew and was curious only about trying it the way you tried it in DB2 and PG. Turns out SQL Server still doesn't support multiple ORDER BYs in a UNION query, even with FETCH FIRST and brackets around each SELECT. Pity. – Andriy M Apr 5 '16 at 6:20
-1
WITH CTE AS
(
SELECT model, price
from   PC 
UNION ALL
SELECT model, price
from   Printer 
UNION ALL
SELECT model, price
from   Laptop 
)
SELECT distinct C.model, C.price 
FROM   CTE as C INNER JOIN Product as P 
ON     C.model = P.model 
WHERE  P.maker = 'B';
  • While this may (or may not) satisfy the requirement an explanation of how it does so and why you've chosen the structure you have would improve it considerably. – Michael Green Oct 6 '17 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.