Data Management

10+ tips for working smarter with SQL

The sharper your SQL skills become, the more robust and error-free your solutions will be. Here are a dozen practical tips to get you started.

The sharper your SQL skills become, the more robust and error-free your solutions will be. Here are a dozen practical tips to get you started.


Your users probably don't know a thing about SQL, but you know its value. It's everywhere, it's easy to learn, and SQL solutions are simple to implement. Whether you use SQL a lot or sparingly, working smarter with SQL will help you avoid errors and improve performance.

Many SQL are vendor-specific. The following tips apply to Jet and Transact-SQL, but most SQL products are similar.

Note: This article is also available as a PDF download.

1: Working with Jet SQL in the Access SQL window

Access users create SQL statements every time they create a query; they just don't know it. They're using the Query Design window, a graphic representation of the actual SQL. Most users can't create the appropriate SQL statements they need, so the graphical interface is helpful, but it can help you too. If you can't create errorless SQL code off the top of your head, let the SQL window help out:

  • Create a query using the Query Design window and then copy the code from the SQL window into a VBA module for use in code.
  • When a query in code returns one of VBA's meaningless error messages, copy the SQL statement to the SQL window and run it. Access will usually return better information about the error than VBA.

You can't always copy a SQL statement straight from the module to the SQL window. If the statement includes concatenation or variables, add a Debug.Print statement right after the definition statement. Then, if you need to debug the statement, copy the evaluated statement from the Immediate window. For instance, the following statement in code won't run in the SQL window because of the variables and concatenation:

"SELECT * FROM " & tbl & " ORDER BY " & fld

However, if you follow the statement with a Debug.Print statement, you can copy the evaluated results, which follow, from the Immediate window:

SELECT * FROM Employees ORDER BY HireDate

2: Words reserved by SQL

SQL reserves several words, such as keywords. Avoid using these words to name columns, tables, variables, or any objects. Their use outside of the context in which the engine expects them confuses the engine and generates errors, or worse -- incorrect results.

3: The difference between ALL, DISTINCT, and DISTINCTROW

SQL's SELECT statement supports three predicates: ALL, DISTINCT, and DISTINCTROW. ALL is the default and returns all records that fulfill any specified conditions. DISTINCT limits the results to unique values in a specific column or columns. For instance, the following statement would return only one record for each LastName value:

SELECT DISTINCT LastName

In other words, if you have a record for both John and Mary Smith, this statement returns only one record for Smith. However, DISTINCT works with all the columns, not just the column that immediately follows. That means, this statement will return a record for both John and Mary because the combined columns don't produce a duplicate record:

SELECT DISTINCT LastName, FirstName

If the SELECT clause references more than one column, the combination of values from all the selected columns must be unique for a given record.

DISTINCT has a few quirks you should know about but might not expect:

  • Don't use DISTINCT with the asterisk character (*). You must specify columns by name.
  • Any query using DISTINCT isn't updatable, which makes sense.

While DISTINCT works with columns, DISTINCTROW works with records. (Transact-SQL doesn't support DISTINCTROW.) This predicate has a few limitations of its own, which can make an error hard to troubleshoot:

  • The engine ignores DISTINCTROW if there's only one table in the query.
  • The engine ignores DISTINCTROW if you reference all columns from all of the underlying tables.

4: Retrieving only what you need

It's tempting to use the asterisk character (*) when retrieving data via a SELECT clause, but don't, unless you really need to retrieve all columns. The more data you retrieve, the slower your application will perform. For optimum performance, retrieve only the columns you need.

5: Variations in aggregates

Both SQL and VBA support aggregate functions, but not the same aggregates. Although they aren't interchangeable, you can often use one or the other. Table A compares the two types within the context of Jet and Transact-SQL.

Table A

T-SQL Jet VBA Explanation Considers Null
Avg Avg DAvg Returns the mean or average of the specified domain No
Count Count DCount Counts the number of non-Null values in the specified column No
Count(*) Count(*) DCount(*) Counts the number of rows Yes
Sum Sum DSum Totals the values in the specified column No
Min Min DMin Returns the smallest value No
Max Max DMax Returns the largest value No
First Returns the value from the first row in the specified column Yes
Last Returns the value from the last row in the specified column Yes
StDev StDev DStDev Returns sample standard deviation No
StDevP StDevP DStDevP Returns population standard deviation No
Var Var DVar Returns sample variance No
VarP VarP DVarP Returns population deviation No

Don't use VBA's domain aggregates when a SQL aggregate will do. When you must use VBA's domain aggregates, apply an index to the underlying column for best performance. Keep in mind that although SQL's GROUP BY doesn't eliminate null values, most SQL aggregates don't evaluate them. If you want null values considered, you must accommodate them in the expression.

6: GROUP BY considerations

SQL's GROUP BY clause defines subsets of data. The most important thing to remember when including a GROUP BY clause is to include only those columns that define the subset or summarize data for the subset. In other words, a GROUP BY can't include extraneous data. For instance, to learn the number of orders placed on a specific date, you'd use a statement similar to the following:

SELECT OrderDate, Count(OrderID)

FROM Orders

GROUP BY OrderDate

This query would return one record for each date. Each record would display the date and the number of orders for that date. You can't include any other columns.

GROUP BY is versatile. You don't need to specify a column in the SELECT clause to group by it. For instance, you could omit OrderDate from the above query and return just the count for each date (although the results wouldn't make much sense). As long as the GROUP BY column is in the source, SQL doesn't require it in the SELECT clause. On the other hand, if you refer to a column in the SELECT clause, you must also include it in the GROUP BY clause or in an aggregate function. For instance, the following statement doesn't work because the Freight column isn't part of an aggregate or the GROUP BY clause:

SELECT OrderDate, Count(OrderID) AS TotalForDate, Freight

FROM Orders

GROUP BY OrderDate

In truth, it doesn't really make sense to try to include a column in this way. If you want the Freight data within the context of a GROUP BY query, you probably want a summary of the freight values in the group, as follows:

SELECT OrderDate, Count(OrderID) Max(Freight)

FROM Orders

GROUP BY OrderDate

Jet can't group a Memo or OLE Object column. In addition, you can't include a GROUP BY clause in an UPDATE statement, which makes sense. SQL would have no way of knowing which record to update.

7: Altering table structure

There are several SQL statements for altering the design of a table. Always approach such a task with care because you could destroy data. For instance, if you change a column's data type, the engine might truncate or delete existing data to accommodate the new column's data type. Keep the following limitations in mind when altering tables:

  • You can't alter an existing column's data to COUNTER if the column already contains data.
  • You can't rename a column. You must remove the column using DROP COLUMN and then re-create it using the new name. To preserve the data, create the new column, copy data from the original column using UPDATE, and then delete the original column.
  • Before altering a table using ALTER TABLE, close it. Otherwise, the engine will return an error. The following VBA code will close an open table and inhibit the error that's generated if the table isn't open:
On Error Resume Next

DoCmd.Close acTable, table

On Error GoTo 0
  • You can't delete a column if a constraint exists on that column. First, delete the constraint using DROP CONSTRAINT.
  • Remove a CHECK CONSTRAINT before deleting the table to which it applies.
  • You can't modify an existing relationship. You must delete it and then re-create it.

8: SQL's problem child, INSERT INTO

SQL's INSERT INTO statement lets you add new data to an existing table. Used correctly, INSERT INTO works great, but you must remember one critical thing: Although the table designs don't have to match, the specific columns identified on both sides of the task must match. In short, the table that receives the new data must contain the same columns as the incoming data.

You can also explicitly identify each column using the VALUES clause as follows:

INSERT INTO target (col1, col2, col3, ...)
VALUES(value1, value2, value3, ...)

However, this syntax adds only a single row at a time, so it will have limited use. You can omit the column references only if you supply a value for every column in target. When including the column references, their order must match the order in the table definition. You must include the primary key column, unless that key is an auto-numbering column -- it isn't necessary to include an auto-numbering column.

9: Using UPDATE to delete values

SQL's DELETE statement deletes entire records. The statement won't return an error if you specify a list of columns, but it will ignore the list. For instance, the following statements would delete all the data in a table named Employees:

DELETE
FROM Employees
DELETE Employees.*
FROM Employees
DELETE Employees.Salary
FROM Employees

Even though the last syntax specifies Salary, SQL will delete all the data, not just the Salary values. To delete specific values, use SQL's UPDATE in the form:

UPDATE source

SET source.column = Null

[WHERE condition]

However, even this will return an error if column properties are in conflict. For instance, if a column requires data, it will reject Null.

10: Dropped properties with SELECT INTO

SQL's SELECT INTO creates a new table by copying rows from an existing table using the following form:

SELECT list|* INTO newtable

FROM sourcetable

[WHERE condition]

However, this statement doesn't copy the table exactly. It won't copy the original table's primary key, indexes, or column and table properties (beyond the default settings). In addition, it ignores Caption settings and uses the original column names.

When including a SELECT INTO statement, keep in mind that this statement will replace an existing table if one of the same name (newtable) exists within its scope. Fortunately, SQL will warn you first. In some cases, the engine will delete the existing table before it actually creates the new one, so if something goes wrong, you can't rely on the original table because it'll be gone. Before running a SELECT INTO, it's a good idea to make a copy of the existing newtable, if one exists. In addition, if newtable is open, SQL will return an error.

You don't have to copy data to the new table. You can create a new empty table by adding a WHERE clause as follows:

SELECT * INTO newtable

FROM source

WHERE FALSE

SQL will create newtable but copy no data to it because no record can satisfy the FALSE condition.

11: The difference between WHERE and HAVING

The WHERE and HAVING clauses perform similar functions but they aren't interchangeable. WHERE limits the data returned by the SELECT clause; therefore, a GROUP BY is inconsequential. The engine compares data and eliminates records that don't satisfy the WHERE clause before it groups the records. On the other hand, the HAVING clause eliminates data that doesn't satisfy the grouping criteria.

If you have trouble remembering which clause to use, remember that the WHERE clause is positioned before the GROUP BY clause and the engine applies the WHERE clause before grouping the records.

12: UNION matchup

SQL's UNION operator lets you combine records from different sources using the following form:

SELECT1 list|*

UNION

SELECT2 list|*

The important thing to remember with a UNION is that the column order in both SELECT statements must match. The column names don't have to match, but each list must contain the same number of columns and their data types must be compatible. If the data types don't match, the engine sometimes chooses the most compatible for you. The results might work, but then again, they might not.

By default, UNION sorts records by the values in the first column because UNION uses an implicit DISTINCT predicate to omit duplicate records. To include all records, including duplicates, use UNION ALL, which eliminates the implicit sort. If you know there are no duplicate records, but there are a lot of records, you can use UNION ALL to improve performance because the engine will skip the comparison that's necessary to sort (to find duplicates).


Finally: 10 Things... the newsletter!

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. Automatically sign up today.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
Tony Hopkinson
Tony Hopkinson

Group By doesn't elimiate nulls was a bit iffy, almost as though it should... Getting your head round nullability is one of the key things you have to do to get round SQL. And why would anyone think select into was a copy???? I think it would have been polite to explain that SQl in access is quite non-standard in places but that you can use widely applicable sql in it. Relying on things like distinctrow is a mistake, always try to get to compatible sql if you can. Not saying don't use it, but know that bit won't work if you port away from your current dbms.

csmith.kaze
csmith.kaze

I cannot express how important that is. If you are doing this from a LAMP(or similar) or a program on the client, always clean your sql inputs! users don't know that certain characters are bad and there are difinately disasters that can arise. And if this is a webapp on the internet, this is doubly so. SANITIZE YOUR SQL INPUTS! http://xkcd.com/327/

Second and Goal
Second and Goal

There are a couple minor issues with it: "SELECT OrderDate, Count(OrderID) AS TotalForDate, Freight FROM Orders GROUP BY OrderDate" You mention that it doesn't really make sense to include a column such as Freight in this query. Not only does it not make sense, but the above query won't execute - it's invalid syntax. When using aggregate functions in a query, all fields must either be an aggregate function, or included in the group by clause, or the query won't run. Also, when discussing WHERE vs. HAVING, you state "WHERE limits the data returned by the SELECT clause; therefore, a GROUP BY is inconsequential." That couldn't be more untrue. Looking at your original query of number of orders grouped by date, a where clause could be used in conjunction with a group by clause to return all the number of orders, grouped by date, for a specific date range (specified in the WHERE clause). Sure, you might be able to achieve the same thing using HAVING, but HAVING is nowhere near as efficient as WHERE on most database engines (including SQL Server, using TRANSACT-SQL), and you'll take a noticeable performance hit, especially on larger tables. Overall, though, a lot of good pointers for people starting out with SQL.

Shellbot
Shellbot

there's some handy info in there.. I'm unsure of your target audience though..was it meant for a "User" using Access ? I'm not so sure about this though: " It?s everywhere, it?s easy to learn, and SQL solutions are simple to implement." I've worked on/with some serious SQL solutions that were neither easy or simple..

Editor's Picks