The SELECT StatementSELECT is the most frequently used SQL statement. It retrieves data from one or more tables. At a minimum, SELECT takes two clauses: the data to be retrieved and the location to retrieve it from. NOTE Technically, SELECT statements don't even need table names. It is perfectly valid (although uncommon) to do something like this: SELECT 100 AS id This statement returns a single row with a single columndata is not retrieved from any table at all. In practice, however, most basic SELECT statements are made up of four parts that must appear in this order:
Specifying the Data to RetrieveYou specify the data to be retrieved by listing the required table column names as the first clause (right after the keyword SELECT). At least one column must be specified. NOTE The maximum number of columns (or the width of a retrieved row) that may be retrieved varies from one DBMS (Database Management System) to the next. There is no standard size or limitation. The following example retrieves a single column (named product_id) from a table (named products): SELECT product_id FROM products To retrieve multiple columns, you must separate the column names with commas: SELECT product_id, product_name FROM products NOTE To retrieve unique values, use the DISTINCT keyword before the column name. You also can retrieve all columns without listing them individually. You do so by using the wildcard character * as follows: SELECT * FROM products TIP For performance reasons, it is generally not a good idea to use SELECT * (unless you actually need every column). As a rule, retrieve just what you need and nothing more. You can also rename columns when retrieving them by assigning aliases to them. You do so by using the AS keyword, which lets you specify an alternative name for a column, as follows: SELECT product_id AS id, product_name AS name FROM products In this example, the returned columns are id and name, even though the actual table names are product_id and product_name. TIP You most often use aliases when working with aggregate functions (covered in Chapter 46, "Aggregates"), but another important use allows you to rename illegally named columns. For example, some databases allow you to include spaces and special characters (such as the # sign or plus and minus signs) in column names, and these characters could render the column names unusable in other applications (such as ColdFusion). Using aliases, you can rename the columns, giving them safe (and legal) names when they are retrieved. Specifying the TableThe table name is always specified using the FROM keyword, as follows: SELECT product_id, product_name FROM products Data can be retrieved from multiple tables, in which case commas must separate the table names. You usually do so only in join operations.
Joins are covered in Chapter 45, "Joins." NOTE Some databases require that table names be fully qualified (with a prefix that indicates the table owner and database). FilteringRetrieved data is filtered using the WHERE clause, which must contain one or more filter conditions using supported operators. Table 44.1 lists the operators supported by most SQL implementations.
The basic mathematical type operators are used as follows: SELECT product_id, product_name FROM products WHERE product_id=1 You use the IN operator to specify multiple values, which must be separated by commas and enclosed within parentheses: SELECT product_id, product_name FROM products WHERE product_id IN (1,3,7,18,45) TIP You can use lists and the list functions to pass values to the IN operator. See Chapter 13, "Lists," for more information. You use the LIKE operator for wildcard searches. The SQL specification supports three wildcards, as shown in Table 44.2 (although some databases do not support them all).
You use wildcard searches to search for patterns within column text. The following example finds all products beginning with the letter s: SELECT product_id, product_name FROM products WHERE product_name LIKE 's%' Wildcards can be used anywhere within a string, not just at the beginning. The following example finds all products that contain the text widget: SELECT product_id, product_name FROM products WHERE product_name LIKE '%widget%' TIP Wildcard matches are generally the slowest form of filter. This is particularly true of wildcards used in the start of a search pattern. And as such, they should not be overused unnecessarily. You can combine searches by using the AND and OR operators. The following example finds all products with the text widget in the name and a cost of $5 or more: SELECT product_id, product_name FROM products WHERE product_name LIKE '%widget%' AND product_price >= 5 CAUTION When using multiple search conditions (using AND and OR), you should use parentheses to group clauses appropriately. They prevent ambiguity and prevent clauses from being evaluated in an unexpected order. To negate a condition, you use the NOT operator before the condition as follows: SELECT product_id, product_name FROM products WHERE NOT product_id IN (1,3,7,18,45) SortingRetrieved data can be sorted using the ORDER BY clause. Data can be sorted in one or more columns .If more than one column is specified, data is sorted by the first column and then by the second, if multiple rows have the same first column value, and so on. The following example retrieves all products and sorts them by name: SELECT product_id, product_name FROM products ORDER BY product_name Data is sorted in ascending or descending order, which you specify by using the ASC or DESC keywords, respectively. If neither keyword is provided, ASC is assumed by default. |