User's Guide
   PART 1. Working with Databases
     CHAPTER 4. Queries: Selecting Data from a Table       

Query overview

A query requests data from the database and receives the results. This process is also known as data retrieval. All SQL queries are expressed using the SELECT statement.

     This chapter assumes a familiarity with very simple queries. For an introduction to queries, see Selecting Data from Database Tables.

  Queries are made up of clauses

You construct SELECT statements from clauses. In the following SELECT syntax, each new line is a separate clause. Only the more common clauses are listed here.

SELECT select-list
[ FROM table-expression ]
[ ON search-condition ]
[ WHERE search-condition ]
[ GROUP BY column-name ]
[ HAVING search-condition ]
[ ORDER BY { expression | integer } ]

The clauses in the SELECT statement are as follows:

• The SELECT clause specifies the columns you want to retrieve. It is the only required clause in the SELECT statement.

• The FROM clause specifies the tables from which columns are pulled. It is required in all queries that retrieve data from tables. In the current chapter, the table-expression is a single table name. SELECT statements without FROM clauses have a different meaning, and we ignore them in this chapter.

• The ON clause specifies how tables in the FROM clause are to be joined. It is used only for multi-table queries and is not discussed in this chapter.

• The WHERE clause specifies the rows in the tables you want to see.

• The GROUP BY clause allows you to collect aggregate data.

• The HAVING clause specifies rows on which aggregate data is to be collected.

• By default, rows are returned from relational databases in an order that has no meaning. You can use the ORDER BY clause to sort the rows in the result set.

Most of the clauses are optional, but if they are included then they must appear in the correct order.

     For a complete listing of the syntax of the SELECT statement syntax, see SELECT statement.

    This chapter discusses only the following set of queries:

• Queries with only a single table in the FROM clause. For information on multi-table queries, see Joins: Retrieving Data from Several Tables.

• Queries with no GROUP BY, HAVING, or ORDER BY clauses. For information on these, see Summarizing, Grouping, and Sorting Query Results.

  Entering queries

In this manual, SELECT statements and other SQL statements are displayed with each clause on a separate row, and with the SQL keywords in upper case. This is not a requirement. You can enter SQL keywords in any case, and you break lines at any point.

Keywords and line breaks 

For example, the following SELECT statement finds the first and last names of contacts living in California from the Contact table.

SELECT first_name, last_name 
FROM Contact
WHERE state = 'CA'

It is equally valid, though not as readable, to enter this statement as follows:

SELECT first_name, 
last_name from contact
wHere state
 = 'CA'

Case sensitivity of strings and identifiers 

Identifiers (that is, table names, column names, and so on) are case insensitive in Adaptive Server Anywhere databases.

Strings are case sensitive by default, so that 'CA', 'ca', 'cA', and 'Ca' are equivalent, but if you create a database as case-sensitive then the case of strings is significant. The sample database is case insensitive.

Qualifying identifiers 

You can qualify the names of database identifiers if there is ambiguity about which object is being referred to. For example, the sample database contains several tables with a column called city, so you may have to qualify references to city with the name of the table. In a larger database you may also have to use the name of the owner of the table to identify the table.

SELECT dba.contact.city
FROM contact
WHERE state = 'CA'

Since the examples in this chapter involve single-table queries, column names in syntax models and examples are usually not qualified with the names of the tables or owners to which they belong.

These elements are left out for readability; it is never wrong to include qualifiers.

The remaining sections in this chapter analyze the syntax of the SELECT statement in more detail.