SELECT Statement

FROM Clause

Single Table

Multiple Tables (Joins)

Inner Joins - Join occurs in the where clause

Outer Joins - Left Outer Join, Right Outer Join

Nested Selects

Single Table

SELECT * FROM CUSTOMERS

Multiple Tables (Joins)

Join columns must have matching data types and lengths.

SELECT company, order_number FROM CUSTOMERS, ORDERS ...

Inner Joins

Tables are joined in the WHERE clause. The join predicate is 'AND'ed 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