Inner joins

In an inner join, the derived tables contain only rows that satisfy the join condition.

Simple inner joins

A simple inner join selects rows from the Cartesian product of two tables.

Example

Select customer names and the associated order numbers from the CUSTOMERS and ORDERS tables:

SELECT company, order_num	or	SELECT company, order_num
FROM customers, orders		FROM customers JOIN orders
WHERE customers.cust_num = orders.cust_num		ON customers.cust_num = orders.cust_num

Customers who have not placed an order, e.g. Freddy’s Fishery with the customer number 104, are not included in the derived table.

company             order_num    
Login GmbH          300    
JIKO GmbH           200    
The Poodle Parlor   250    
The Poodle Parlor   251    
The Poodle Parlor   305    
Foreign Ltd.        210    
Foreign Ltd.        211 

Example

Select the service associated with each order.

SELECT o.order_num, o.order_text, o.order_stat, s.service_num, s.service_text FROM orders o INNER JOIN service s ON o.order_num = s.order_num order_ order_ order_ service_ service_ num text stat num text 200 Staff training 5 1 Training documentation 200 Staff training 5 2 Training 200 Staff training 5 3 Training 211 Database design customers 4 4 Systems analysis 211 Database design customers 4 5 Database design 211 Database design customers 4 6 Copies/transparencies 211 Database design customers 4 7 Manual 250 Mailmerge intro 2 10 Travel expenses 250 Mailmerge intro 2 11 Training

Multiple inner joins

A multiple inner join selects columns from the Cartesian product of more than two tables.

Example

Select the service provided for each customer who has placed an order from the CUSTOMERS, ORDERS and SERVICE:

SELECT c.company, o.order_num, s.service_num	or	SELECT c.company, o.order_num, s.service_num
FROM customers c, orders o, service s		FROM customers c JOIN orders o
WHERE c.cust_num=o.cust_num		ON c.cust_num=o.cust_num
AND o.order_num=s.order_num		JOIN service s ON o.order_num=s.order_num

company           order_num  service_num
JIKO GmbH         200        1
JIKO GmbH         200        2
JIKO GmbH         200        3
Foreign Ltd.      211        4
Foreign Ltd.      211        5
Foreign Ltd.      211        6
Foreign Ltd.      211        7
The Poodle Parlor 250       10
The Poodle Parlor 250       11