Introducing the SELECT Statement
The SELECT statement is constructed of clauses that let you look at data in a relational database. These clauses let you select columns and rows from one or more database tables or views, specify one or more conditions, order and summarize the data, and put the selected data in a temporary table.
This chapter shows how to use five SELECT statement clauses. You must include these clauses in a SELECT statement in the following order:
1. SELECT clause
2. FROM clause
3. WHERE clause
4. ORDER BY clause
5. INTO TEMP clause
Only the SELECT and FROM clauses are required. These two clauses form the basis for every database query because they specify the tables and columns to be retrieved. Use one or more of the other clauses from the following list:
Two additional SELECT statement clauses, GROUP BY and HAVING, let you perform more complex data retrieval. They are introduced in Chapter 3, "Composing Advanced SELECT Statements." Another clause, INTO, specifies the program or host variable to receive data from a SELECT statement in SQL APIs. Complete syntax and rules for using the SELECT statement are shown in Chapter 1 of the Informix Guide to SQL: Syntax.
Some Basic Concepts
The SELECT statement, unlike the INSERT, UPDATE, and DELETE statements, does not modify the data in a database. It simply queries the data. Whereas only one user at a time can modify data, multiple users can query on or select the data concurrently. The statements that modify data appear in Chapter 4, "Modifying Data." The INSERT, UPDATE, and DELETE statements appear in Chapter 1 of the Informix Guide to SQL: Syntax.
In a relational database, a column is a data element that contains a particular type of information that occurs in every row in the table. A row is a group of related items of information about a single entity across all columns in a database table.
You can select columns and rows from a database table; from a system-catalog table, a file that contains information on the database; or from a view, a virtual table created to contain a customized set of data. System catalog tables are shown in Chapter 1 of the Informix Guide to SQL: Reference. Views are discussed in Chapter 11, "Granting and Limiting Access to Your Database," of this manual.
Privileges
Before you query data, make sure you have the database Connect privilege and the table Select privileges. These privileges are normally granted to all users. Database access privileges are discussed in Chapter 11, "Granting and Limiting Access to Your Database," of this manual and in the GRANT and REVOKE statements in Chapter 1 of the Informix Guide to SQL: Syntax.
Relational Operations
A relational operation involves manipulating one or more tables, or relations, to result in another table. The three kinds of relational operations are selection, projection, and join. This chapter includes examples of selection, projection, and simple joining.
Selection and Projection
In relational terminology, selection is defined as taking the horizontal subset of rows of a single table that satisfies a particular condition. This kind of SELECT statement returns some of the rows and all of the columns in a table. Selection is implemented through the WHERE clause of a SELECT statement, as Query 2-1 shows.
Query 2-1
Query Result 2-1 contains the same number of columns as the customer table, but only a subset of its rows. Because the data in the selected columns does not fit on one line of the DB-Access or ROM Interactive Schema Editor (ISED) screen, the data is displayed vertically instead of horizontally
.
In relational terminology, projection is defined as taking a vertical subset from the columns of a single table that retains the unique rows. This kind of SELECT statement returns some of the columns and all of the rows in a table.
Projection is implemented through the select list in the SELECT clause of a SELECT statement, as Query 2-2 shows.
Query 2-2
Query Result 2-2 contains the same number of rows as the customer table, but it projects only a subset of the columns in the table
.
The most common kind of SELECT statement uses both selection and projection. A query of this kind, shown in Query 2-3, returns some of the rows and some of the columns in a table.
Query 2-3
Query Result 2-3 contains a subset of the rows and a subset of the columns in the customer table.
Joining
A join occurs when two or more tables are connected by one or more columns in common, creating a new table of results. The query in the example uses a subset of the items and stock tables to illustrate the concept of a join, as Figure 2-1 shows.
Query 2-4 joins the customer and state tables.
Query 2-4
Query Result 2-4 consists of specified rows and columns from both the customer and state tables
.
The Forms of SELECT
Although the syntax remains the same across all Informix products, the form of a SELECT statement and the location and formatting of the resulting output depends on the application. The examples in this chapter and in Chapter 3, "Composing Advanced SELECT Statements," display the SELECT statements and their output as they appear when you use the interactive Query-language option in DB-Access or the SQL Editor. You can embed SELECT statements in a language such as INFORMIX-ESQL/C (where they are treated as executable code).
Special Data Types
The examples in this chapter use the INFORMIX-Universal Server database server, which enables database applications to include the data types VARCHAR, CLOB, BLOB, TEXT, and BYTE.
With DB-Access or the SQL Editor, when you issue a SELECT statement that includes one of these three data types, the results of the query are displayed differently:
Differences specific to VARCHAR, CLOB, BLOB, TEXT, and BYTE are noted as appropriate throughout this chapter.
You can issue a SELECT statement that queries on NCHAR columns instead of CHAR columns. If you are using Universal Server, you can query on NVARCHAR columns instead of VARCHAR columns.
For complete GLS information, see the Guide to GLS Functionality. For additional information on GLS and other data types, see Chapter 9, "Implementing Your Data Model," in this manual, and Chapter 2 of the Informix Guide to SQL: Reference. 
|
