![]() |
![]() |
|
![]() |
Local SQL Part 4 The WHERE Clause © 2001 Lawrence G. DiGiovanni Digico, Inc. www.digicoinc.com Preface Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users. This is the fourth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article will focus on the use of the WHERE clause in simple SQL SELECT statements. Introduction In a previous article, we discussed the general form of the SQL SELECT statement, including the most common clauses and predicates. We used the SQL SELECT and FROM clauses to query and return all rows of a table. The WHERE clause of the SQL SELECT statement is used to determine which rows of the base table(s) are to be included in the result set. The WHERE clause is followed by a list of conditions which is evaluated for each row of the table(s) being queried. For rows in which the condition list evaluates to True, the row is included in the result set. For rows in which the condition list evaluates to False, the row is excluded from the result set. As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database. WHERE Example A common type of condition found in a WHERE clause is one which tests for equality between a column value and some given literal expression. From our example database, we could find all customers based in California with the following query: SELECT Company FROM customer WHERE State = 'CA'Executing this query produces an ANSWER table consisting of a single column, Company, listing the company names of all of our California-based companies. The result set is equivalent to the result set produced by the following QBE query: customer | Company | State | | CheckPlus | CA |In our SQL example, we used a test for an exact comparison (State = 'CA'). We can also test using other comparison operators. The below query lists the Item Description and List Price of all of our inventory items that list for more than $45.00. SELECT Description, ListPrice FROM parts WHERE ListPrice > 45Which is equivalent to the QBE query: parts | Description | ListPrice | | CheckPlus | CheckPlus >45 |How about if we wanted to list items which were greater than $45 but less than $100? In QBE, this would look like: parts | Description | ListPrice | | CheckPlus | CheckPlus >45,<100 |In SQL, it would look like: SELECT Description, ListPrice FROM parts WHERE ListPrice > 45 AND ListPrice < 100Note the use of multiple WHERE conditions, separated by "AND". If we wanted items which were less than $45 or greater than $100, our QBE would look like: parts | Description | ListPrice | | CheckPlus | CheckPlus <45 OR >100 |Our SQL would look like: SELECT Description, ListPrice FROM parts WHERE ListPrice < 45 OR ListPrice > 100Again, we used multiple WHERE conditions, separated by "OR" this time. More Complex WHERE Clauses The below query lists orders that totaled more than $3000 or orders that were paid with Visa and totaled more than $2000. SELECT orderno, PaymentMethod, itemstotal FROM orders WHERE itemstotal > 3000 OR (itemstotal >2000 AND PaymentMethod = 'Visa')Note the use of parentheses to group the terms of the WHERE clause. Special Operators Local SQL has special operators to handle common query requirements. BETWEEN Consider a SQL query that lists the description and list price of all of our parts which cost between $45 and $100, inclusive. We could write this query as: SELECT Description, ListPrice FROM parts WHERE ListPrice >= 45 AND ListPrice <= 100Or, we could use the BETWEEN operator: SELECT Description, ListPrice FROM parts WHERE ListPrice BETWEEN 45 AND 100Note that the BETWEEN operator is inclusive of the start and end criteria. IN Consider a SQL query that lists the names of customers from California, Hawaii or Florida: SELECT name FROM customer WHERE State = 'CA' OR State = 'HI' OR State = 'FL'Or, we could use the IN operator: SELECT name FROM customer WHERE State IN ('CA','HI','FL')IN can also reference columns of other tables or queries. We’ll explore that capability in a future article. Summary The WHERE clause of a SQL SELECT statement restricts the result set (ANSWER table) to rows meeting specific requirements by evaluating criteria lists. Where the criteria list evaluates to TRUE, the row is returned. The criteria lists typically consist of comparisons between table columns and literal values or calculated expressions. There are several SQL operators which may also be used. Local SQL Part 5 Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|