|
SQL Reference |
SELECT Statement |
WHERE
|
WHERE Clauseleft_operand = right_operand (equal to) left_operand < right_operand (less than) left_operand > right_operand (greater than) left_operand <= right_operand (less than or equal to) left_operand >= right_operand (greater than or equal to) left_operand <> right_operand (not equal to) left_operand != right_operand (not equal to) left_operand in (value-list | Nested Select) left_operand like right_operand_with_wildcard exists (Nested Select) - MUST RETURN ONLY ONE COLUMN. =,
<, >, <=, >=, <>, like - Nested Select that GUARANTEES
to return ONLY ONE ROW. MUST RETURN ONLY ONE COLUMN. NOT, AND, OR with any of the above criteria predicates
left_operand and right_operand can contain any of the following:
left_operand = right_operand (equal to)SELECT company, contact FROM customers where company='systems' SELECT customers.company, orders.order_no FROM customers, orders WHERE
left_operand < right_operand (less than)SELECT company FROM customers WHERE customer_no < 100
left_operand > right_operand (greater than)SELECT customer_no, product_no FROM orders WHERE status='back'
AND
left_operand <= right_operand (less than or equal to)SELECT * FROM inventory WHERE onhand <= 10
left_operand >= right_operand (greater than or equal to)SELECT company FROM customers WHERE customer_no < 100
left_operand <> right_operand (not equal to)SELECT company, state FROM customers WHERE state <> 'CO'
left_operand != right_operand (not equal to)SELECT company, state FROM customers WHERE state != 'CA'
left_operand in (value-list | Nested Select)SELECT company, state FROM customers WHERE state IN ('A?', 'C?') SELECT company, state FROM customers WHERE state NOT IN ('AZ', 'CA') SELECT * FROM orders WHERE total > (SELECT avg(total) FROM orders)
left_operand like right_operand_with_wildcardSELECT company, state FROM customers WHERE state like 'C?' SELECT company, state FROM customers WHERE company = 'sys%'
exists (Nested Select)Evaluates false if nested select returns 0 rows, true otherwise. In this example, all customer records will be returned. SELECT c.company FROM customers c WHERE exists (SELECT * FROM
orders o
=, <, >, <=, >=, <>, !=, like (Nested Select)These selects MUST return only one column and only one row. SELECT * FROM orders WHERE customer_no = (SELECT customer_no
FROM customers SELECT customer_no, order_date, STATUS FROM orders WHERE product_no
like
in (Nested Select)SELECT distinct customer_no, status, product_no FROM orders
WHERE product_no in
NOT, AND, ORThe following two statements return identical results, but note the placement of the NOT operator. SELECT company, state FROM customers WHERE company='%systems
or software' SELECT company, state FROM customers WHERE company='%systems
or software' The next two statements return identical results, but note the differences in the WHERE clause with the Omnidex sentinel character and OR operator. SELECT company, state FROM customers WHERE company='%systems or software' SELECT company, state FROM customers WHERE company='systems'
OR The next two statements return companies located in Boulder CO, Boulder Creek CA, and Denton TX, but NOT Denver CO. SELECT company, city, state FROM customers WHERE (city like
'boul%' SELECT company, city, state FROM customers WHERE (city like
'boul%'
|
|
APIs | SQL Reference | Stored Procedures | Third Party Tools | Debugging |
OmniAccess API |
ODBC |
JDBC |
Nested Queries |
SET Operations |
WITH Options |
CURSOR / INSTANCE |
Commands |
Functions |
SQL Server |
Oracle |
MS Access |
Ms Excel |