The SELECT statement, unlike 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 or select the data concurrently. For more information about statements that modify data, see Modifying Data. The syntax descriptions of the INSERT, UPDATE, and DELETE statements appear in the IBM 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 special table 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 described in the IBM Informix: Guide to SQL Reference. Views are discussed in the IBM Informix: Database Design and Implementation Guide.
Before you make a query against data, make sure you have the Connect privilege on the database and the Select privilege on the table. These privileges are normally granted to all users. Database access privileges are discussed in the IBM Informix: Database Design and Implementation Guide and in the GRANT and REVOKE statements in the IBM Informix: Guide to SQL Syntax.
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.
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 the columns in a table. Selection is implemented through the WHERE clause of a SELECT statement, as Figure 5 shows.
SELECT * FROM customer WHERE state = 'NJ'
Figure 6 contains the same number of columns as the customer table, but only a subset of its rows. In this example, DB–Access displays the data from each column on a separate line.
customer_num 119 fname Bob lname Shorter company The Triathletes Club address1 2405 Kings Highway address2 city Cherry Hill state NJ zipcode 08002 phone 609-663-6079 customer_num 122 fname Cathy lname O'Brian company The Sporting Life address1 543d Nassau address2 city Princeton state NJ zipcode 08540 phone 609-342-0054
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 the rows in a table.
Projection is implemented through the projection list in the Projection clause of a SELECT statement, as Figure 7 shows.
SELECT city, state, zipcode FROM customer
Figure 8 contains the same number of rows as the customer table, but it projects only a subset of the columns in the table. Because only a small amount of data is selected from each row, DB–Access is able to display all of the data from the row on one line.
city state zipcode Sunnyvale CA 94086 San Francisco CA 94117 Palo Alto CA 94303 Redwood City CA 94026 Los Altos CA 94022 Mountain View CA 94063 Palo Alto CA 94304 Redwood City CA 94063 Sunnyvale CA 94086 Redwood City CA 94062 Sunnyvale CA 94085
·
·
·
Oakland CA 94609 Cherry Hill NJ 08002 Phoenix AZ 85016 Wilmington DE 19898 Princeton NJ 08540 Jacksonville FL 32256 Bartlesville OK 74006
The most common kind of SELECT statement uses both selection and projection. A query of this kind returns some of the rows and some of the columns in a table, as Figure 9 shows.
SELECT UNIQUE city, state, zipcode FROM customer WHERE state = 'NJ'
Figure 10 contains a subset of the rows and a subset of the columns in the customer table.
city state zipcode Cherry Hill NJ 08002 Princeton NJ 08540
A join occurs when two or more tables are connected by one or more columns in common, which creates a new table of results. Figure 11 shows a query that uses a subset of the items and stock tables to illustrate the concept of a join.
Figure 12 joins the customer and state tables.
SELECT UNIQUE city, state, zipcode, sname FROM customer, state WHERE customer.state = state.code
Figure 13 consists of specified rows and columns from both the customer and state tables
city state zipcode sname Bartlesville OK 74006 Oklahoma Blue Island NY 60406 New York Brighton MA 02135 Massachusetts Cherry Hill NJ 08002 New Jersey Denver CO 80219 Colorado Jacksonville FL 32256 Florida Los Altos CA 94022 California Menlo Park CA 94025 California Mountain View CA 94040 California Mountain View CA 94063 California Oakland CA 94609 California Palo Alto CA 94303 California Palo Alto CA 94304 California Phoenix AZ 85008 Arizona Phoenix AZ 85016 Arizona Princeton NJ 08540 New Jersey Redwood City CA 94026 California Redwood City CA 94062 California Redwood City CA 94063 California San Francisco CA 94117 California Sunnyvale CA 94085 California Sunnyvale CA 94086 California Wilmington DE 19898 Delaware