When writing SQL queries it’s important to keep a few things in mind. You want to organize your statement so that it’s readable and easily understood by those that might follow. You also want to keep in mind the performance characteristics of the query.

SELECT * FROM Customers

There are a few things we might want to do with this statement. For starters, maybe we don’t need all information from the Customers table at this time. Perhaps for our purposes we only need the Customer’s first name and last name.

SELECT FirstName, LastName FROM dbo.Customers

For better performance and clarification “dbo” was added. This tells SQL Server within which schema to look for the Customers table.

As the query begins to grow break the statement across multiple lines at SQL keywords. You can see below the table has been aliased so that we can reference it without having to type all of the schema/table name for each column. While not yet necessary, this is in anticipation for joining tables which might have columns of the same name. Also, WITH(NOLOCK) was added so that the table will not be locked and performance will not be disrupted in the event that the SELECT query takes a long time to execute.

SELECT c.FirstName, c.LastName
FROM dbo.Customers AS c WITH(NOLOCK)

Here, for example, the Address and States tables have been added to the query so that we might return the address information for the customer. Also, the number of results have been limited to a single customer in the WHERE clause. You can see the use of INNER JOINs to return data that exists in all 3 tables, and the join criteria matches left to right, c.CustomerID = a.CustomerID. A lot of times joins are written incorrectly with the criteria reversed (a.CustomerID = c.CustomerID) and that could potentially lead to confusion. The ANSI standard is to have the table/columns displayed as shown.

SELECT c.FirstName, c.LastName, a.Address1, a.City, s.StateAbbr
FROM dbo.Customers c WITH(NOLOCK)
INNER JOIN dbo.Address a WITH(NOLOCK) ON c.CustomerID = a.CustomerID
INNER JOIN dbo.States s WITH(NOLOCK) ON a.StateID = s.StateID
WHERE c.CustomerID = 1

And finally, a LEFT OUTER JOIN is added to return Order information, if it exists, for our customer.

SELECT c.FirstName, c.LastName, a.Address1, a.City, s.StateAbbr, o.OrderNumber
FROM dbo.Customers c WITH(NOLOCK)
INNERJOIN dbo.Address a WITH(NOLOCK) ON c.CustomerID = a.CustomerID
INNER JOIN dbo.States s WITH(NOLOCK) ON a.StateID = s.StateID
LEFT OUTER JOIN dbo.Orders o WITH(NOLOCK) ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = 1

###

For more about SQL Syntax please reference the following links:

Microsoft Developer Network – Join Fundamentals
Wikipedia – SQL