SQL query with left join to get rows in decreasing order from a specific date

Question

I have two tables:

Product

productID, productName, productPrice

Purchased

productID, creationDate

Query - return a list of unique products that were bought since June 2nd with most expensive product returned first

For this I wrote below query:

SELECT Product.productID,
       Product.productName,
       Product.productPrice,
       Purchased.creationDate
FROM   Product
       LEFT JOIN Purchased
              ON Product.productID = Purchased.productID
ORDER BY Product.productPrice DESC
WHERE  Purchased.creationDate > 02 / 06 / 2014 

Could you please confirm if above written query is correct or I am doing any mistake.

Answer 1

Try this:

SELECT p.productID, p.productName, p.productPrice, pp.creationDate
FROM Product p
LEFT JOIN Purchased pp ON p.productID = pp.productID
WHERE pp.creationDate > '2014-06-02'
ORDER BY p.productPrice DESC;

Answer 2

And make it shorter using alises

SELECT pr.productID,
       pr.productName,
       pr.productPrice,
       pu.creationDate
FROM   Product pr
       LEFT JOIN Purchased pu
              ON pr.productID = pu.productID
WHERE  pu.creationDate > 02 / 06 / 2014
ORDER  BY pr.productPrice DESC 

Answer 3

I would do a inner join instead of a left join if I do not need to get those products that were never bought at all (Meaning no entry in Pruchased table). Joins Explained

and the order by should be the last thing to do.

SELECT Product.productID, Product.productName, Product.productPrice, Purchased.creationDate
FROM Product
INNER JOIN Purchased
ON Product.productID=Purchased.productID
WHERE  Purchased.creationDate > '2014-06-02'
ORDER BY Product.productPrice DESC