|
 in
(Nested Select) - Can return 0 or more rows. 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:
 Use
parenthesis to force order of precedence.
left_operand = right_operand (equal to)
 SELECT
company, contact FROM customers where company='systems'
SELECT
customers.company, orders.order_no FROM customers, orders
WHERE
customers.customer_no=orders.customer_no
AND (orders.status='shipped' OR
orders.status='pending')
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
(quantity*amount)
> 50 ORDER BY 1, 2
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)
SELECT
company, state FROM customers WHERE state in ('A?', 'C?')
SELECT
company, state FROM customers WHERE state NOT in ('AZ', 'CA')
left_operand like right_operand_with_wildcard
SELECT
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
WHERE
o.status='back')
=, <, >, <=, >=, <>,
!=, 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
WHERE
company='Dynamic Information Systems')
SELECT
customer_no, order_date, STATUS FROM orders WHERE product_no
like
(SELECT
product_no FROM products WHERE product_no='SUP16210')
in (Nested Select)
SELECT
distinct customer_no, status, product_no FROM orders WHERE
product_no in
(SELECT
product_no FROM products WHERE product_name='monitor')
NOT, AND, OR
The following two statements return identical results, but
note the placement of the NOT operator.
SELECT
company, state FROM customers WHERE company='%systems or software'
AND
state NOT in ('AZ', 'CA')
SELECT
company, state FROM customers WHERE company='%systems or software'
AND
NOT state in ('AZ', 'CA')
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
company='software'
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%'
AND
state in ('CO', 'CA')) OR (city like 'den%' AND state != 'CO')
SELECT
company, city, state FROM customers WHERE (city like 'boul%'
AND
state in ('CO', 'CA')) OR (city like 'den%' AND NOT state
= 'CO')
top
|
Select
List Items