In [10]:
--Millist toodet ei olnud ühtegi korda tellitud - Rel Algebra tehted + alampäring
SELECT * FROM Products
WHERE Productid IN ( 
    SELECT  ProductID FROM Products
    EXCEPT
    SELECT DIstinct ProductID FROM  [Order Details])
(2 rows affected)
Total execution time: 00:00:00.015
Out[10]:
ProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
78Uus toode711 l bottles42,300050110
79r4NULLNULLNULL0,00000000
In [8]:
--Millist toodet ei olnud ühtegi korda tellitud - LEFT JOIN
SELECT DISTINCT P.*
FROM Products P LEFT JOIN [Order Details] O ON O.ProductID=P.ProductID
WHERE O.ProductID IS NULL
(2 rows affected)
Total execution time: 00:00:00.011
Out[8]:
ProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
78Uus toode711 l bottles42,300050110
79r4NULLNULLNULL0,00000000
In [11]:
--Millist toodet ei olnud ühtegi korda tellitud - alampäring
SELECT * FROM Products
WHERE Productid NOT IN ( 
    SELECT DIstinct ProductID FROM  [Order Details])
(2 rows affected)
Total execution time: 00:00:00.013
Out[11]:
ProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
78Uus toode711 l bottles42,300050110
79r4NULLNULLNULL0,00000000

Toodet mi

  • JOIN - LEFT/RIGHT
  • Rel.Algebra tehted
  • Alampäringud