Search ConditionShorthand Simple Booleancheck Multiple searchconditions NULL check ,ch03.13605 Page 140 Wednesday, November 29, 2000 4:42 PM Table 3-9: Search Conditions Using the WHERE Clause Syntax WHERE [NOT] expressioncomparison_operator expression WHERE [NOT] expressioncomparison_operator expression{AND | OR} expression comparison_operatorexpression WHERE [NOT] column_name IS [NOT] NULL Example SELECT au_idFROM authors WHERE au_id = ‘172-32-1176′ SELECT au_idFROM authors WHERE au_lname NOT LIKE ‘John%’ SELECT au_id FROM authors WHERE au_id = ‘172-32-1176′ AND au_lname = ‘White’ SELECT * FROM titles WHERE price IS NULL Usage & Description The operators <, >, <>, >=, <= , and = can be used when comparingexpressions. There are also a numberof special comparison operators, suchas LIKE, described later in this table. The keyword NOT checks for theinverse of any Boolean check basedon the regular operators <, >, <>, >=, <= , and =, in addition to specialoperators such as LIKE, NULL, BETWEEN, IN, EXISTS, ANY, and ALL. AND merges multiple conditions andreturns results when both conditionsare true. AND takes priority over otheroperators. Parentheses in the WHERE clausefurther affect the priority of operators. OR merges multiple conditions andreturns results when either conditionis true. OR takes priority after AND. IS NULL and IS NOT NULL tell thequery to check for null values (or allvalues except null values).
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 141 Wednesday, November 29, 2000 4:42 PM Table 3-9: Search Conditions Using the WHERE Clause (continued) Search ConditionShorthand Syntax Example Usage & DescriptionJOIN check WHERE [NOT] column_value(s) [(+)]=[(+)] column_value(s) OrWHERE [NOT] column_value(s) [*]=[*] column_value(s) SELECT a.au_lname, a.au_fname, t2.title FROM authors a, titleauthor t1, titles t2WHERE a.au_id = t1.au_idAND t1.title_id = t2.title_idORDER BY t2.title JOIN checks can be performed byevaluating the common key betweentwo or more tables. Outer joins areaccomplished in PostgreSQL byadding the asterisk to the side whereall records should be retrieved. Outerjoins in Oracle are accomplished byadding the plus sign in parentheses(+) to the side where null values areallowed (basically, the opposite of theasterisk method). Refer to theprevious section on JOINs for more information. LIKE check WHERE [NOT] column_name [NOT] LIKE ‘match_string’ /* get any phone number starting with415 */ SELECT * FROM authorsWHERE phone LIKE ‘415%’ LIKE tells the query to use patternmatching on the string in quotationmarks. The wildcard symbols aredetailed under the LIKE entry. EXISTence check WHERE [NOT] EXISTS (subquery) SELECT p1.pub_nameFROM publishers p1WHERE EXISTS(SELECT * FROM titles t1WHERE pub_id =p1.pub_idAND type = ‘psychology’) EXISTS is always used in conjunctionwith a subquery; rather than returningdata, the subquery is a Boolean test ofwhether the data exists. This examplewill return all publishers ofpsychology books. BETWEEN range check WHERE [NOT] expression [NOT] BETWEEN expression ANDexpression SELECT * FROM titlesWHERE ytd_sales BETWEEN 4000 AND 9000 BETWEEN performs an inclusiverange check. It is the same as WHERE (expression >= x and expression <= y).
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 139 Wednesday, November 29, 2000 4:42 PM In examining the query, note that parentheses impact the order in which WHERE criteria are processed according to Operators Precedence. The database s default sort order determines how the WHERE clause retrieves results sets for a query. For example, Microsoft SQL Server is (by default) dictionary-order and case-insensitive, making no differentiation between Smith , smith , and SMITH . But Oracle uses dictionary-order and case-sensitive, finding the values Smith , smith , and SMITH to be unequal. There are more specific capabilities of the WHERE clause than what is illustrated in the example. Table 3-9 helps provide a quick summary of the common capabilities of the WHERE clause. Statements SELECT 139
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 138 Wednesday, November 29, 2000 4:42 PM WHERE a.au_id = t1.au_id AND t1.title_id = t2.title_id ORDER BY t2.title — ANSI style query with multiple tables SELECT a.au_lname, a.au_fname, t2.title FROM authors a JOIN titleauthor AS t1 ON a.au_id = t1.au_id JOIN titles AS t2 ON t1.title_id = t2.title_id ORDER BY t2.title Multi-key Join Example: –theta style query with multipart key SELECT s1.store_id, s1.title_id, s2.qty FROM sales s1, sales_projections s2 WHERE s1.store_id = s2.store_id AND s1.title_id = s2.title_id ORDER BY s1.store_id, s2.title_id — ANSI style query with multipart key SELECT s1.store_id, s1.title_id, s2.qty FROM sales s1 JOIN sales_projections s2 ON s1.store_id = s2.store_id AND s1.title_id = s2.title_id ORDER BY s1.store_id, s2.title_id The WHERE clause The WHERE clause is an extremely potent component of the SELECT statement. The WHERE clause provides most of the search conditions that cull unwanted data from the query; the remaining search conditions are satisfied by the HAVING clause (explained later in this section). A poorly written WHERE clause can ruin an otherwise beautiful SELECT statement, so the nuances of the WHERE clause must be mastered thoroughly. This is an example of a typical query and a multipart WHERE clause: SELECT a.au_lname, a.au_fname, t2.title, convert(char,t2.pubdate) FROM authors a JOIN titleauthor t1 ON a.au_id = t1.au_id JOIN titles t2 ON t1.title_id = t2.title_id WHERE (t2.type = ‘business’ OR t2.type = ‘popular_comp’) AND t2.advance > $5500 ORDER BY t2.title 138 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 137 Wednesday, November 29, 2000 4:42 PM Right [Outer] Join Specifies that all records be returned from the table on the right side of the join statement, even if the table on the left has no matching record. Columns from the left table return NULL values. (In the example, all records in the jobs table are returned with or without a matching record in the employee table (currently supported by Microsoft SQL Server): — Oracle theta style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e, jobs j WHERE j.job_id = (+) e.job_id — ANSI style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e RIGHT JOIN jobs j ON e.job_id = j.job_id Full Join Specifies that all rows from either table be returned, regardless of matching records in the other table. The result set shows NULL values where no data exists in the join (currently supported by Microsoft SQL Server): — theta style does not support this — function — ANSI style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e FULL JOIN jobs j ON e.job_id = j.job_id Joins in the ANSI style are actually easier to understand than those in theta style, since the query itself clearly indicates which table is on the left in a LEFT JOIN and which table is on the right in a RIGHT JOIN. The syntax to perform a similar query with multipart keys and multiple tables joined together is largely an extension of the same technique. Multi-table Join Example –theta style query with multiple tables SELECT a.au_lname, a.au_fname, t2.title FROM authors a, titleauthor t1, titles t2 Statements SELECT 137
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 136 Wednesday, November 29, 2000 4:42 PM — ANSI style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e CROSS JOIN jobs j Inner Join Specifies that unmatched rows in either table of the join should be discarded. If no join type is explicitly defined in the ANSI style, then this is the default (currently supported by Microsoft SQL Server, PostgreSQL and MySQL): — theta style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e, jobs j WHERE e.job_id = j.job_id — ANSI style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e JOIN jobs j ON e.job_id = j.job_id Left [Outer] Join Specifies that all records be returned from the table on the left side of the join statement. If a record is returned from the left table has no matching record in the table on the right side of the join, it is still returned. Columns from the right table return NULL values. (In this case, all employees are returned whether they have a job description or not.) Many professionals recommend configuring outer joins as left joins wherever possible for consistency (currently supported by Microsoft SQL Server): — Oracle theta style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e, jobs j WHERE j.job_id (+) = e.job_id — ANSI style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e LEFT JOIN jobs j ON e.job_id = j.job_id 136 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 135 Wednesday, November 29, 2000 4:42 PM The JOIN clause In non-ANSI standard implementations, the join operation is performed in the WHERE clause (described in the section on WHERE clauses). In the ANSI SQL-92 standards, joins are performed in the JOIN clause of the query. These join methods are known as the theta style and the ANSI style of joins, respectively. To retrieve joined data from two or more tables, the tables first must share a meaningful relationship. The tables to be joined must possess a column or columns that share a common set of values that allow the tables to be meaningfully linked. This column, or columns, is called the join key or common key. Most but not all of the time, the join key is the primary key of one table and a foreign key in another table. As long as the data in the columns match, the join can be performed. In the PUBS database, both the employee table and the jobs table contain a job_id column. Thus, job_id is the common key between the employee and jobs tables. To perform a query using an ANSI-style join, list the first table and the keyword JOIN, followed by the table to be joined. Once the second table is typed in, type the keyword ON and the join condition that would have been used in the old style query. The following shows the original query now in ANSI style: SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee AS e JOIN jobs AS j ON e.job_id = j.job_id ORDER BY e.fname, e.lname Join types These problems are solved by the use of join types in the ANSI style and the equal-asterisk ( =* ) combination for Microsoft SQL Server or plus-asterisk ( +* ) for Oracle in theta joins. The following list shows how to control this behavior in joins: Cross Join Specifies the complete cross product of two tables. For each record in the first table, all the records in the second table are joined, creating a huge result set. This command has the same effect as leaving off the join condition and is also know as a Cartesian Product. Cross joins are not advisable or recommended (currently supported by Microsoft SQL Server): — theta style SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e, Jobs j Statements SELECT 135
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 134 Wednesday, November 29, 2000 4:42 PM — the schema, table, and then column name must be listed! FROM employee, salesadmin.sales_summary WHERE employee.emp_id = salesadmin.sales_summary.emp_id ORDER BY employee.emp_id; Literal expressions may be used as a select list item. Mathematics calculations can be entered as a select list item. In Microsoft SQL Server, no FROM statement is needed. In Oracle, the calculation should be executed against the system table called DUAL. The table allows the SELECT command to retrieve values where no table exists. For example: –QUERY (Microsoft) SELECT 2 + 2 –QUERY (Oracle) SELECT 2 + 2 FROM dual –RESULTS 4 The FROM clause The FROM clause generally serves two purposes: to list the tables and views where a query retrieved its data (with a comma between each tablename); and to assign an alias for long table names, making coding lengthy queries a lot easier. An alias can be assigned in the FROM clause by two means: by typing the table- name, a space, and the alias; or by typing the tablename, AS, and the alias. The example below illustrates each of these techniques. An example of a query that extracts data from multiple tables might have a FROM and WHERE clause that is coded in the following manner: SELECT e.emp_id, e.fname, e.lname, j.job_desc FROM employee e, jobs j WHERE e.job_id = j.job_id ORDER BY e.fname, e.lname Once an alias has been assigned in a query, be sure to use it exclusively for table references within that query. Do not mix references to the full table name and the alias in a query. This query retrieves the emp_id (first and last name of each employee stored in the employee table) and joins the job_id of the employee, which is a code number, with the full job description found in the JOBS table. 134 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 133 Wednesday, November 29, 2000 4:42 PM ALL is the default, meaning all records are returned, including defaults. DISTINCT is a keyword that tells the query to filter out all duplicate records. Thus, the result set includes only one instance of identical records. There are several other rules for what can appear in the SELECT item list: Most commonly, all the columns desired should be listed out using a comma between each one. An asterisk (*) serves as shorthand to retrieve all the columns in every table shown in the FROM clause, as they are listed in the CREATE TABLE statement. Column aliases are added in to replace the default column headings used in the results. Use the format column AS alias or column alias. This is especially useful when a column heading is too cryptic or lengthy to be readily understood. For example: — alias format SELECT au_lname AS “Last Name” FROM authors — alternative alias format SELECT au_lname “Last Name” FROM authors Local and global variables, where supported, may appear as a select list item. Comments may be dispersed throughout any SQL or Transact-SQL statement by using either the double-dash (–) or the slash-asterisk (/* … */ ). The double-dash causes the query to ignore any text that follows the double-dash until the end of line. The slash causes the query to ignore any text within the slash-asterisk and inverse slash-asterisk. The table name should be prefixed to the column name in a query using multiple tables. Technically, the table name needs to apply to any column in both tables; it is commonly considered good practice to do so anyway. For example, both the jobs and employee tables contain the job_id column: SELECT employee.emp_id, employee.fname, employee.lname, jobs.job_desc FROM employee, jobs WHERE employee.job_id = jobs.job_id ORDER BY employee.fname, employee.lname The schema or owner name should be prefixed to a column when extracted from a context outside of the current user. If the table is owned by another username, then the username must be included in the column reference. For example, assume that this example query is run in the PUBS database but also retrieves data from the SALES database: SELECT employee.emp_id, salesadmin.sales_summary.total_amt Statements SELECT 133
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 131 Wednesday, November 29, 2000 4:42 PM SAVEPOINT command is that transactions may be partially rolled back to a unique savepoint marker using the ROLLBACK command. Vendor SQL Server Supported, with variations MySQL Not supported Oracle Supported PostgreSQL Not supported Command SQL99 Syntax and Description SAVEPOINT savepoint_name Some vendors allow duplicate savepoint names within a transaction, but this is not recommended. Substitute savepoint identifiers (in the format :X) also may be included to enable DBMS to track the savepoint with an integer rather than a name. Not all vendors support this approach, and it is not recommended as the best practice. Note that SQL99 supports the statement RELEASE SAVEPOINT savepoint_name, enabling an existing savepoint to be eliminated. However, this statement is not supported by any of the vendors covered in this book. Microsoft SQL Server Syntax and Variations SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable} Microsoft SQL Server does not support the SAVEPOINT command. Instead, it uses the SAVE command. Rather than declaring the literal name of the savepoint, you can reference a variable containing the name of the savepoint. When the ROLLBACK TRAN savepoint_name command is executed, SQL Server rolls the transaction back to the appropriate savepoint, then continues processing at the next valid Transact-SQL command following the ROLLBACK statement. Finally, the transaction must be concluded with a COMMIT or a final ROLLBACK statement. Oracle Syntax and Variations SAVEPOINT savepoint_name Oracle fully supports the SQL99 implementation. Example This example performs several data modifications, rolls back to a savepoint, and then rolls back the transaction completely: INSERT INTO sales VALUES(’7896′,’JR3435′,’Oct 28 1997′,25,’Net 60′,’BU7832′); SAVEPOINT after_insert; UPDATE sales SET terms = ‘Net 90′ WHERE sales_id = ‘7896′; Statements SAVEPOINT 131
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services