0
\$\begingroup\$

I am using the northwind example database to brush up my SQL server skills.

I want to select all the products ordered by the customer with the customer id 'Folko'.

My idea is to join the Orders table with the OrderDetails table on OrderID and then join the Products table with the OrderDetails table on ProductID. Then insert the WHERE clause (i.e. where Orders.CustomerID = 'FOLKO')

Here is the query:

select Products.ProductName from [Order Details] 
inner join Orders on [Order Details].OrderID = Orders.OrderID
inner join Products on [Order Details].ProductID = Products.ProductID
where Orders.CustomerID = 'FOLKO'

I have also managed to get the same information with the following query:

select Products.ProductName from [Order Details], Orders, Products
where Products.ProductID = [Order Details].ProductID
and Orders.OrderID = [Order Details].OrderID
and Orders.CustomerID = 'FOLKO'

I am getting the result that I want but I wonder if these are the preferred ways (or if one or the other is better) or if it should be done in a different way altogether?

\$\endgroup\$
  • \$\begingroup\$ You should avoid old style joins (second). \$\endgroup\$ – paparazzo Jan 16 '18 at 19:24
1
\$\begingroup\$

The first query, i.e. using explicit JOINs, is generally the preferred way, and has benefits like:

  • verbosity
  • easier to read and maintain
  • less likely to have accidental cross joins

For more precise and detailed answers, see the answers to this question on SO (bearing in mind it is tagged , it still is relevant for ). Also read this related blog post by Baron Schwartz.

\$\endgroup\$
  • \$\begingroup\$ Perfect! Thank you for the help and useful links! \$\endgroup\$ – karra Jan 16 '18 at 19:33

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.