| Lesson 7 || The SELECT statement |
| Objective || Gain an overview of the SELECT statement. |
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.
||9761 Vivian St
||26859 Leroy St
||7586 N Wisner Ave
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 command retrieves data from the database.
This is one of the most often used and complex SQL commands. The basic syntax is:
[ 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