The Select Statement
The general form of the select statement is:
select
column(s)
from
table(s)
[
where
predicate
]
[
order by
column(s)
];
We start with a simple example – Get Name, Address, and City for customers in California.
select name, address, city
from customer
where state = 'CA';
Result
| Name | Address | City |
|---|---|---|
| America West Hospital | P.O. Drawer 3333 | San Diego |
| Stunning Software Corp. | 9876 N. Dakota St. | Fresno |
| 3A Software | 1600 Technology Circle | La Mesa |
| Zorro Cutlery | 12121 SE 12th Street | San Diego |
| All Canada Brewing Company | 1111 Main Ave | Montreal |
The above example demonstrates the most common form of the select statement. It is important to stress that the result of the query on the customer table is another table that is derived from existing tables. Because of this fact, the retrieval part of SQL is called a closed system. The result of a SQL select statement is always a table.
Select Column List
The column list specified in a select statement is made up of column names separated by commas. You can use just the name of the column or a qualified name. The above statement using qualified names would look like:
select customer.name, customer.address, customer.city
from customer
where state = 'CA';
Using qualified names is optional if the name is unique in the set of columns. However, if there are multiple columns with the same name, you should use qualified names to avoid confusion. For example, if we have an account manager table that contains columns ID and Name and we relate to that table from Customer, we could create the query:
select customer.name, address, city, accntmngr.name
from customer, accntmngr
where customer.accntmngr_id = accntmngr.id and
customer.state = 'CA';
In this case, we must use qualified column names; otherwise, it would not be clear which name we are referring to. This statement joins two tables. More on joins will follow.
If you want to select all columns in a table, the shortcut * has been defined. To select all columns of all rows of the customer table, we would create the following query:
select *
from customer;
From Table List
The table list that is specified in a select statement is made up of table names separated by commas. You should specify all tables involved in the query here.
Where Predicate
The predicate following the optional where clause filters the data selected by the select statement. It is made up of a number of conditions combined by logical operators. It is possible to use a subselect statement in the predicate. For example, if we want to select all orders with an amount above the average order amount, we could create the query:
select *
from orderhea
where order_total > (select avg(order_total)
from orderhea);
Order by Column List
The column list that is specified in the order by clause of a select statement is made up of column names separated by commas. You can use just the name of the column or a qualified name.
We adjust our original sample to – Get Name, Address, and City for customers in California, ordering the result by customer name.
select name, address, city
from customer
where state = 'CA'
order by name;
Result
| Name | Address | City |
|---|---|---|
| 3A Software | 1600 Technology Circle | La Mesa |
| All Canada Brewing Company | 1111 Main Ave | Montreal |
| America West Hospital | P.O. Drawer 3333 | San Diego |
| Stunning Software Corp. | 9876 N. Dakota St. | Fresno |
| Zorro Cutlery | 12121 SE 12th Street | San Diego |
The columns in the order by list may or may not appear in an index. This is a major difference from the way DataFlex and other record-oriented systems work. SQL can order by any column that is included in the statement; some columns may be excluded because of restrictions in size and type of columns allowed in the order by list.
Even if the columns appear in an index, there is no guarantee that the index will actually be used to retrieve the desired information. SQL systems have a component called the "optimizer." This component will analyze the SQL query and decide if and how indexes are used. Sometimes it may be more efficient to scan the entire table instead of using an index. The optimizer uses database statistics gathered by the database system to base its decisions on. Some servers allow you to influence the optimizer’s decision by adding so-called "hints" to a select statement.
Joining Tables
The item that should not be left undiscussed is joining tables. A join is a relational operator; it defines how two tables need to be "glued together" to retrieve information from them. There are a number of different join types. The most common type is the equi-join.
In an equi-join, we glue two tables together based on the equivalence of one or more columns in those tables. An example was already given above in the statement:
select customer.name, address, city, accntmngr.name
from customer, accntmngr
where customer.accntmngr_id = accntmngr.id and
customer.state = 'CA';
The "where customer.accntmngr_id = accntmngr.id" is the "join part" of the statement. Joins can be specified as part of the predicates following a where clause. They can also be specified in the join section of a from clause.
select customer.name, address, city, accntmngr.name
from customer join accntmngr
on customer.accntmngr_id = accntmngr.id
where customer.state = 'CA';
This form has the advantage that relational operators are clearly separated from filter predicates.
There are other join operators that will not be discussed here; these include cross-join, natural join, and outer joins. Not all SQL dialects support the join operator. Refer to your database’s documentation for more information.