SELECT is a targeted operation that has a zillion options, including several that are vendor-specific. This means that nearly every database vendor – including Microsoft – has its own special extensions to the syntax. Therefore, vendor-specific SELECT queries can pose quite a challenge for admins, so here’s an example of a worst-case SELECT statement:
SELECT TOP 10 PERCENT
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate > '2010-01-01' AND o.Status LIKE '%Open%' AND
c.CustomerID IN (SELECT CustomerID FROM LoyalCustomers) ORDER BY
o.OrderDate DESC, o.Status ASC
It’s a lot to read, right? Well, here’s the broken down version:
SELECT TOP 10 PERCENT
Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
o.OrderDate > '2010-01-01'
AND o.Status LIKE '%Open%'
AND c.CustomerID IN
(SELECT CustomerID FROM LoyalCustomers)
Below is an even more detailed rundown of what’s going on with this SELECT query statement. This can also be used as kind of a template for interpreting other SELECT queries you may need to translate:
- The SELECT keyword starts things off, telling us that this is a data-retrieval query.
- The TOP 10 PERCENT part is optional and many queries don’t include it. This tells SQL Server to grab only the first 10% of the rows that match the query after sorting them as specified. Other vendors’ database products usually won’t include this Microsoft-specific extension. Instead, they’ll add a LIMIT clause to the very end of the query.
- The three field names specify the columns that will be included in the query’s results. The c. and o. are nicknames, or aliases, for two tables in the database, so we’re getting two columns from one table and one column from the second table.
- The FROM clause tells us which tables are included in the query.
- Customers c specifies the Customers table, and assigns it an alias of “c” for this query. This is the only place where the “c” alias is defined and the definition only applies to this query. This is what allows us to enter c.CustomerName in the column list. If we didn’t specify an alias, we’d have to type “Customers.CustomerName” using the full table name, which is inconvenient to type.
- The INNER JOIN means that the query will only include those rows that have a matching value in the CustomerID column in both the Customers and Orders tables. By joining these two tables, we can get a result set that lists every order placed by every customer.
- In this example, the WHERE clause specifies three criteria that must be met in order for rows to be included in the result set. The first two, which filter by OrderDate and Status, are pretty straightforward. Note that dates are enclosed like strings in single quotes, and the greater than (>) operator means “later in time” when used in date comparisons. Also notice the use of the LIKE operator, which permits percent (%) to be used as a string wildcard.
- The third WHERE criteria is a subquery. Since it’s in parentheses, it will be executed first and its results will be used in place of the subquery. The IN operator checks to see if the value – the CustomerID, in this example – exists within a list of possible values. Here, that list is created by the subquery, so we’re only getting order information for orders placed by customers who are in our LoyalCustomers list.
- Finally, the ORDER BY clause changes the sort order. It’s sorting OrderDate in descending order first – meaning newer dates are first – followed by the Status field in ascending order. Ascending is the default, so if you don’t see DESC or ASC, then it’s ascending.
These SELECT queries can get complicated, but by breaking them down they’re a lot easier to interpret and figure out.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.
This was first published in August 2010