The Select Statement

---

The Select Statement

The Select statement is used to retrieve a set of rows from one or more tables and is probably the most commonly used statement in the SQL language. Here is its syntax:

Select <selectexpression> [,<selectexpression>]…
From <tableref> [,<tableref>]…
[Where <expression>]
[Order By <expression> [Asc|Desc] [, <expression> [Asc|Desc] ] …
Where
<selectexpression> ::= * | 
    <selectitem> [ [As] <alias> ]
<selectitem> ::= <column> | 
    <table>.<column> | 
    <tablealias>.<column>
    <expression> | 
    <function> ( <expression> )
<function> ::= Count |
    Max |
    Min |
    Sum
<tableref> ::= <table> |
    <table> <tablealias> 

Where <alias> is an alternate name of a column, <tablealias> is an alternate name of a table and, <expression> is a valid expression.

Note 

The Select statement is the most complicated statement in the SQL language. Although the syntax in the preceding code might seem intimidating, keep in mind that it represents only a small part of the full syntax for a Select statement. However, the syntax listed here is generally supported by all database vendors, so you can use it with most applications.

Simple Select Statements

Rather than spend a lot of time trying to learn the syntax rules for the Select statement, you’re going to see a series of examples for common situations that illustrate how use the Select statement.

Retrieving Everything

The following Select statement retrieves all the columns and rows from the Customers table:

Select *
From Customers

The asterisk (*) indicates that all the columns in the table should be retrieved, and the From clause indicates that the command should pull the records from the Customers table.

Retrieving a List of Columns

Suppose that you don’t want to retrieve all the columns from a table. You can use this Select statement to retrieve only the columns that you are planning to use. This query retrieves only the CustomerId and Name columns from the Customers table.

Select CustomerId, Name
From Customers

Tip 

Only Take What You Really Need
Although using an * to retrieve columns makes your SQL statement easier to type, it can cause problems. The more data you retrieve from a database, the longer the query will take to process and the more additional memory will be required to hold the results. Granted, the extra time and space might be negligible for many queries, but it’s still a good programming practice to request only the resources you really need.

Retrieving Rows

Both of the prior examples retrieve all the rows of data from the database. Although doing so can be useful in many situations, it can also cause significant problems, too. Imagine that you’re working for a large company whose tables contain millions of rows of data. Retrieving all that data would overwhelm any computer. Instead of returning every row from a table, if all you really want to do is to retrieve the rows associated with a particular customer or for a particular Zip code, you can use the Where clause to pick just the rows you want.

Important 

If your Select statement doesn’t contain a Where clause, you should probably ask if you really need all the data or if you just need some of the rows. Although it can be appropriate to retrieve all the rows from a table (for example, when you want to copy a table to a worksheet), in most cases it isn’t necessary and could have a negative impact on your database server’s performance.

Using Simple Search Expressions

Using the Where clause entails creating an expression that identifies the rows you want to retrieve. If the expression is True, the row will be returned; otherwise, it’ll be ignored.

For example, the following Select statement retrieves only the rows where the CustomerId is 101. Because the CustomerId column is the primary key for this table, this statement will always return a single row from the table.

Select *
From Customers
Where CustomerId = 101

Notice that you can use other comparison operators in the Where clause. In addition to the equals sign (=), you can use the less than sign (<), the greater than sign (>), the less than or equal to sign (<=), the greater than or equal to sign (>=), or the not equal to sign (<>).

Using Complex Search Expressions

You can also use other operators such as And, Or, and Not to create more complex expressions. This query chooses every customer that was added during 2003.

Select *
From Customers
Where DateAdded >= ’01-Jan-2003’ And DateAdded <= ’31-Dec-2003’

Note 

The SQL syntax requires that non-numeric values such as character strings and dates should be enclosed in single quotes (‘). Double quotes (“) are used to specify column and table names which contain spaces.

Using Null

You can determine if a column doesn’t have a value in a particular field by using the Is Null expression. In this example, only the rows where the DateAdded field contains a Null value will be returned.

Select *
From Customers
Where DateAdded Is Null

Using the Like Operator

The Like operator is a very powerful tool that lets you search for values in a column using wildcard characters. For instance, the following Select statement matches every customer where the value in the Name column starts with the letters Free:

Select *
From Customers
Where Name Like ’Free*’

Table 22-1 contains a list of wildcard characters that you can use with the Access database. Remember that these characters can be used in any combination, as in the following Select statement, which retrieves all customers whose names begin with D, E, or F:

Select *
From Customers
Where Name Like ’[D-F]*’ 

Table 22-1: Wildcard Characters in Access

Character	Meaning	Examples
*	Matches zero or more characters.	A* True: A, AA, AB, ACC False: B, BA A*A True: AA, AAA, ABA, ACCA False: AB, AAB *A* True: A, AB, CAC, DADD False: B, BB, CCC
?	Matches a single character.	A? True: AA, AB, AC False: AAA, BA
#	Matches a single digit.	A# True: A0, A9 False: AA, A00
[]	Matches the character or range of characters specified inside the brackets. Remember that using an exclamation point (!) means that the characters outside the range will match the value.	A[*]A True: A*A False: AAA A[D-F]A True: ADA, AFA False: AAA, ADAA A[!0-9] True: AA, AB False: A0, A9

When possible, try to limit the use of the Like operators to those columns that are part of an index. Without the index, the database might have to examine each and every row to find the rows that match. And, even with an index, you’ll get the best performance if you use the wildcard characters at the end of the search string.

Note 

Every database system has its own unique set of wildcard characters. For instance, the * used by Access is the % in SQL Server. Be sure to make sure that you’re using the proper wildcard characters in your program.

Sorting Rows

One of the architectural quirks of a relational database is that the database server is free to return rows in any order. However, you can use the Order By clause to force the database server to return in the order you specify. For example, the following Select statement retrieves all the customers from the database in Zip code order:

Select Name, Street, City, State, Zip
From Customers
Order By ZipCode

This Select statement will sort the rows first by Zip code and then within each Zip code by customer’s name.

Select Name, Street, City, State, Zip
From Customers
Order By ZipCode, Name 

Finally, you could use the Desc (short for descending) keyword to reverse the order of the sort in the Select statement. Thus, although the rows will be stored in ascending order by Zip code, within each Zip code, the customer names will be ordered from Z to A (descending order) instead of A to Z (ascending order).

Select Name, Street, City, State, Zip
From Customers
Order By ZipCode, Name Desc

Using Multiple Tables

Thus far, you’ve seen how to use the Select statement with a single table. It’s possible to retrieve rows from multiple tables to create a single virtual table. This is called a join operation. Adding a From clause to a Select statement lets you specify more than one table name. However, joining two tables can be very tricky, and you might not necessarily get the results you would expect.

The Wrong Way to Join Two Tables

If you assume that joining two tables would result in a combination of all the columns from both tables, you would be correct. However, if you assume that the rows are combined intelligently, you would be wrong.

Simply specifying two table names in a Select statement means that the database will combine the first row in the first table with each row in the second table. Then the database will take the second row in the first table and combine it with each row in the second table. This process repeats for each row in the first table, which means that if the first table has 100 rows, and the second table has 200 rows, simply joining the two tables together will return a table containing 20,000 rows.

The Right Way to Join Two Tables

Rather than blindly joining all the rows in the first table with those in the second, you can use the Where clause to identify how the two tables will be joined. Typically, you will join two tables together when a particular value in one table is the same as a value in another table. For example, consider two tables. One table contains customers, whereas the second contains orders placed by customers. Typically, each customer in the Customers table would have a field that uniquely identifies the customer, such as CustomerId.

Likewise, the Orders table would also contain a field that indicates which customer placed the order. Let’s assume that this field is also named CustomerId. Now suppose you want to create a list of all the customers and the orders they placed. You want to join the Customers table with the Orders table, but only when the CustomerId in each table is identical so that you will only join a customer’s information and the customer’s orders.

Resolving Column Names

If you tried to code the preceding example as a Select statement, you would run into a problem because both tables have a CustomerID column. Fortunately, the Select statement lets you use dot notation to combine a table name with a column name to uniquely identify a column when dealing with multiple tables. The form is <tablename>.<columnname>, so in the case of the previous example, you would refer to the columns as Customers.CustomerId and Orders.CustomerId. The resulting Select statement would look like this:

Select *
From Customers, Orders
Where Customers.CustomerId = Orders.CustomerId

Using Aliases

Just as Excel lets you create names you can use to refer to groups of cells, SQL lets you define aliases for a table name. An alias is merely a short name that can be used in place of the table’s name. To define an alias, you follow the name of the table with its alias when you refer to the table in the From clause.

Select *
From Customers c, Orders o
Where c.CustomerId = o.CustomerId

Using Functions

The Select statement lets you perform summary operations over the entire set of rows that would normally be returned. Just as summary operations in Excel (such as SUM, COUNT, or COUNTBLANK) return a single value, the SQL summary functions return a single row containing the result of the function. For example, you can count the number of records a Select statement might return by using the COUNT function to count the number of CustomerId values retrieved.

Select Count(CustomerId)
From Customers 

Other functions available include: MIN, MAX, and AVERAGE. You should be aware that while the COUNT function merely counts each individual row, the other functions work with the value contained in each individual column. Thus, MAX and MIN will return the largest and smallest values found in that column, respectively. The AVERAGE function totals all the values found in that column and then divides by the number of rows. If you apply the AVERAGE function to a column that doesn’t contain numeric values, you will get an error.

---