Overview of SQL SELECT Statement

When many people mention SQL, what they are really talking about is the SELECT statement. This is because the SELECT statement is the foundation for retrieving information from an SQL database system, and most people think of SQL as a query-only language. As you have seen in the lessons prior to this, SQL is quite a lot more. In fact, it's an entire data management language. It allows you to create tables, databases, and indexes and to insert information into those tables. The SELECT statement is key to pulling the information from the database, rather than putting it into the system. You have already seen some simple examples of the SELECT statement where we pulled the information in the customer table:

SELECT * FROM Customer 

What this statement says, in English terms, is, "Get all the rows from the customer table and return them to me." The result is shown below.

CustomerID	LastName	FirstName	Address1	Address2	City	State	Zip Code	Phone
1	Sasser	Sheila	9761 Vivian St		Taylor	MI	48180-3103	313-292-3778
2	Golden	Debbie	26859 Leroy St		Taylor	MI	48180-4871	734-892-9847
3	Schwartz	Kathy	7586 N Wisner Ave		Newaygo,	MI	49347-9715	232-652-1048

You get all columns from the table, with each row showing with the values for each of the columns. In the next lesson, we will examine the syntax of the SELECT statement more closely.

The SELECT Statement retrieves data from the database. This is one of the most often used and complex SQL commands. The basic syntax is:

SELECT select_clause
FROM from_clause
[ WHERE where_clause ]
[ GROUP BY group_by_clause ]
[ ORDER BY order_by_clause [ ASC | DESC ] ]

The parts in square brackets are optional and the vertical bar between ASC and DESC means you can include one or the other of those keywords. The following sections describe these main clauses in more detail.

The SELECT clause specifies the fields that you want the query to return. If a field's name is unambiguous given the tables selected by the FROM clause (described in the next section), you can simply list the field's name as in FirstName. If more than one of the tables listed in the FROM clause have a field with the same name, you must put the table's name in front of the field's name as in Persons.FirstName. The special value * tells the database that you want to select all of the available fields. If the query includes more than one table in the FROM clause and you want all of the fields from a specific table, you can include the table's name before the asterisk as in Persons.* . The following query returns all of the fields for all of the records in the Persons table:

SELECT * FROM Persons

Optionally you can give a field an alias by following it with the keyword AS and the alias that you want it to have. When the query returns, it acts as if that field's name is whatever you used as an alias. This is useful for such things as differentiating among fields with the same name in different tables, for creating a new field name that a program can use for nicer display (for example, changing the CustName field to Customer Name), or for creating a name for a calculated column. A particularly useful option you can add to the SELECT clause is DISTINCT. This makes the database return only one copy of each set of values that are selected. For example, suppose the Orders table contains customer first and last names. The following MySQL query selects the FirstName and LastName values from the table, concatenates them into a single field with a space in between, and gives that calculated field the alias Name. The DISTINCT keyword means the query will only return one of each Name result even if a single customer has many records in the table.

SELECT DISTINCT CONCAT(FirstName, " ", LastName) 
AS Name FROM Orders

The following code shows the Transact-SQL equivalent of this statement:

SELECT DISTINCT FirstName + " " + LastName 
AS Name FROM Orders