SQL SELECT Query

This is a tutorial on the use of the SQL SELECT query, and links to further tutorials on more advanced syntax such as GROUP BY and UNION.




The query can be divided into the following sections:

• Specify what columns are to be returned
• Specify which tables or views to select from
• Specify criteria to limit the rows returned
• Specify the order in which the rows should be returned
• Additional processing, such as grouping, combining multiple result sets, etc

The examples assume 2 tables called Customer and Purchase. I will write an article soon outlining a sample data model that will be used by all tutorials on this site.

SQL SELECT Example

The basic syntax for the SELECT statement is:

SELECT c.Id AS CustomerId, c.FirstName, c.LastName, p.Id AS PurchaseId, p.Item, p.Price, p.Quantity FROM Customer AS c JOIN Purchase AS p ON p.CustomerId = c.Id WHERE c.Id = 12345 ORDER BY p.Price DESC
This example returns a row for each item purchased by customer 12345 and sorts the output from most expensive down to least expensive.

It illustrates several features of the SQL statement. The first thing to point out is what is called aliasing. The first line could have been written like this:

SELECT Customer.Id AS CustomerId, Customer.FirstName, Customer.LastName, Purchase.Id AS PurchaseId, Purchase.Item, Purchase.Price, Purchase.Quantity
You can see that repeatedly including the table names makes it longer, and involves more typing. In the second and third line of the original example I have assigned an alias to each table, allowing the letters c and p to be used instead.

The JOIN describes the relationship between the 2 tables. Each row in the Purchase table stores a purchase for a particular customer, so the CustomerId column is used to represent the customer. SQL does not know about this relationship (although it should have been specified in a foreign key constraint) so it is necessary to include it in the SQL SELECT query.

The WHERE clause allows the rows returned to be limited; in this case it specifies which customer we are interested in.

The ORDER BY clause lists the columns to be used for sorting the result set. Adding DESC ensures the rows are returned in descending order. But what do we do if the customer has purchased several items for the same price and we would like similarly priced items to be ordered by alphabetical order of item name. The only way to guarantee this is to add an additional column to the ORDER BY clause as follows:

ORDER BY p.Price DESC, p.Item
I hope this brief summary has explained the SELECT statement clearly. See the next article, SQL WHERE Clause, which describes the WHERE clause in more detail.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.