Selecting data from a table based on value in another table (SQL server)

Question

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?

• paparazzo: You should avoid old style joins (second).

Answer 1

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 mysql, it still is relevant for sql-server). Also read this related blog post by Baron Schwartz.