Multiple Tables (Joins)
Join columns must have matching datatypes and lengths.
SELECT
company, order_number FROM CUSTOMERS, ORDERS ...
Inner
Joins
Tables are joined in the WHERE
clause. The join predicate is 'AND'd to other WHERE criteria
predicates.
SELECT
company, order_number, status FROM customers, orders WHERE
customers.customer_no
= orders.customer_no AND orders.status='CANCELED'
Outer
Joins
Outer joins prevent the exclusion of records in one table
that do not have records in the joined table.
SELECT
company, order_number, status FROM customers c LEFT JOIN orders
o ON
c.customer_no
= o.customer_no
SELECT
company, order_number, status FROM customers c LEFT OUTER
JOIN
orders
o ON c.customer_no = o.customer_no
SELECT
company, order_number, status FROM customers c RIGHT JOIN
orders o ON
c.customer_no
= o.customer_no
SELECT
company, order_number, status FROM customers c RIGHT OUTER
JOIN
orders
o ON c.customer_no = o.customer_no
Nested Selects
select
company, contact, state from customers c,
(select
distinct customer_no from orders where status='back') AS o
where
c.customer_no = o.customer_no and state!='co'
top
|