The Select Statement

The SELECT statement is by far the most commonly used statement in SQL. This is the statement that allows you to retrieve data from a data source. The following clauses of the SELECT statement are used in this chapter. Only the SELECT and FROM clauses are required to constitute a valid SQL statement:

SELECT [DISTINCT] column1, column2, ... FROM table_name [WHERE restriction_condition] [ORDER BY column_name [ASC|DESC]]

The SELECT clause tells the data source what fields you wish to return. The field names in the SELECT clause are called the SELECT list. The FROM clause tells the data source which table the records should be retrieved from. For instance, a simple example statement could look like this:

SELECT Company, [First Name], [Last Name] FROM Customers

This statement will notify the data source that you want to retrieve all of the values for the Company, First Name, and Last Name fields from the Customers table.

Note that the First Name and Last Name fields in the SQL statement are surrounded by square brackets. This is required for any field or table name that contains spaces or non-alphanumeric characters.

The SELECT statement also provides a shorthand method for indicating that you want to retrieve all fields from the specified table. This involves using a single asterisk as the SELECT list:

SELECT * FROM Customers

This SQL statement will return all fields and all records from the Customers table. It's generally not considered a good practice to use * in the SELECT list, because it leaves your code vulnerable to changes in field names or the order of fields in the table. It can also be very resource intensive with large tables, because all columns and rows will be returned whether or not they are actually needed by the client. However, there are times when it is a useful and time-saving shortcut.

Say that you want to see a list of countries where you have at least one customer located. Simply performing the following query would return one record for every customer in your table:

SELECT [Country/Region] FROM Customers

This resultset would contain many duplicate country names. The optional DISTINCT keyword allows you to return only unique values in your query:

SELECT DISTINCT [Country/Region] FROM Customers

If you only want to see the list of customers located in the U.S., you can use the WHERE clause to restrict the results to only those customers:

SELECT Company, [First Name], [Last Name] FROM Customers

WHERE [Country/Region] = 'USA'

Note that the string literal USA must be surrounded by single quotes. This is also true of dates. Numeric expressions do not require any surrounding characters.

Finally, suppose you would like to have your USA customer list sorted by Company. This can be accomplished using the ORDER BY clause:

SELECT Company, [First Name], [Last Name]

FROM Customers

WHERE Country = 'USA'

ORDER BY Company

The ORDER BY clause will order fields in ascending order by default. If instead you wanted to sort a field in descending order, you could use the optional DESC specifier immediately after the name of the column whose sort order you wanted to modify.

0 0

Post a comment