2 SELECT with INTERSECT or 2 condition with WHERE

I have a task that “Get the makers producing both PCs having a speed of 750 MHz or higher and laptops with a speed of 750 MHz or higher. Result set: maker” from https://www.sql-ex.ru

For this task I wrote this query which is return nothing (empty screen)

SELECT MAKER FROM PRODUCT P
INNER JOIN PC ON PC.MODEL=P.MODEL
INNER JOIN LAPTOP L ON L.MODEL=P.MODEL
WHERE PC.SPEED>=750 AND L.SPEED>=750

​x
 
SELECT MAKER FROM PRODUCT PINNER JOIN PC ON PC.MODEL=P.MODELINNER JOIN LAPTOP L ON L.MODEL=P.MODELWHERE PC.SPEED>=750 AND L.SPEED>=750​

But the right query is

select product.maker from product
inner join pc
on product.model = pc.model   
where pc.speed >= 750

intersect

select product.maker 
from product 
inner join laptop
on product.model = laptop.model
where laptop.speed >= 750

 
select product.maker from productinner join pcon product.model = pc.model   where pc.speed >= 750​intersect​select product.maker from product inner join laptopon product.model = laptop.modelwhere laptop.speed >= 750​

I wonder what is the differences between these two queries and why my query doesn’t get the true output.

Answer

Your version requires that the same product have both characteristics. This is probably not possible — because the same model would need to be both a PC and a laptop.

The intersect version allows the characteristics to be on different products.

Advertisement

Answer