Indicates the condition to evaluate for each record of the table(s) identified by the FROM clause.
•WHERE clause is optional.
•Multiple predicates separated by logical operators OR, AND or NOT must be enclosed in parenthesis. They also can be negated with the NOT operator.
•Date string constants must be enclosed in pound signs (#) like this: #12/31/2015#. The date string must be fully qualified to include the century.
•String constant must be enclosed in single or double quotations ('this is a string', "this is a string").
•Use only single quotes in Delphi as follows:
•IN, BETWEEN and LIKE are fully supported. LIKE with ESCAPE [escape_char syntax is supported]
•Where clause can be used for joining tables when used with '=' and expressions can be used for concatenating more than one column. Left side expression must have only references to first table, right side expression must have only references to right table and so on for the next '='.
SELECT * FROM ... WHERE (UPPER(c.name) + UPPER(c.lastname) =
UPPER(o.FullName)) And (c.CustNo = o.CustNo)
•Logical operators allowed in logical expressions: <, >, <=, >=, <>
•'||' also can optionally be used for concatenating fields instead of '+' operator. This is only for supporting standard syntax.
•Parameterized queries are also supported. For example, using Delphi with TApolloQuery:
ApolloQuery1.SQL.Clear;
ApolloQuery1.SQL.Add( 'SELECT * FROM test WHERE ' +
'(Last = :Last) and (Age = :Age) and (HireDate = :HireDate)');
ApolloQuery1.Params[0].AsString := 'Smith';
ApolloQuery1.Params[1].AsInteger := 90;
ApolloQuery1.Params[2].AsDateTime := EncodeDate( 2000, 2, 29 );
ApolloQuery1.Open;
Examples:
SELECT * FROM test WHERE STATE = 'CA'
SELECT * FROM test WHERE (STATE = 'CA') AND (AGE < 50)
SELECT first, last, state, age FROM test WHERE (STATE = 'CA') AND (AGE < 50)
SELECT * FROM test
WHERE (Last = :Last) and (Age = :Age) and (HireDate = :HireDate)
SELECT Sales + Bonus As TotalSales FROM customers WHERE TotalSales > 1000
SELECT * FROM customer WHERE firstname || lastname = 'JohnDoe'
SELECT * FROM customer
WHERE (county = 'MADRID') OR (county = 'VALENCIA') OR (county = 'BARCELONA')
Always use parenthesis to separate logical operators OR, AND or NOT.
SELECT firstname, lastname FROM credits WHERE age >= 18
SELECT * FROM customer WHERE (age >= 18) AND (age <= 45)
SELECT * FROM customer WHERE age BETWEEN 18 AND 45
SELECT * FROM test WHERE date = '7/1/2015'
Dates must always be in the format of a full year (mm/dd/yyyy).
SELECT * FROM test WHERE date <= '12/31/2015'
SELECT * FROM test WHERE date BETWEEN '7/1/2015' AND '7/31/2015'
SELECT * FROM customer WHERE firstname LIKE 'Al%'