SESAM/SQL V9.1 (en)

If you join more than two tables, you can nest several join expressions.

This allows you to combine inner and outer joins in a single SQL statement.

Examples

The following examples select the customer number, order number and service number from the CUSTOMERS, ORDERS and SERVICE tables. The results depend on the joins used.

Take into account only those customers for whom orders with associated services exist.

SELECT c.cust_num, o.order_num, s.service_num 
   FROM (customers c INNER JOIN orders o ON c.cust_num = o.cust_num)
                     INNER JOIN service s ON o.order_num = s.order_num
   WHERE c.cust_num BETWEEN 100 AND 107
   cust_num     order_num      service_num
   102          200            1
   102          200            2
   102          200            3
   105          250           10
   105          250           11
   106          211            4
   106          211            5
   106          211            6
   106          211            7

Take into account all the customers from the CUSTOMERS table for whom orders exist,regardless of whether these orders have services associated with them. The join expression enclosed in parentheses is the dominant table for the outer join. The NULL value is entered for missing service numbers.

SELECT c.cust_num, o.order_num, s.service_num
   FROM (customers c INNER JOIN orders o ON c.cust_num = o.cust_num)
                     LEFT OUTER JOIN service s ON o.order_num = s.order_num
   WHERE c.cust_num BETWEEN 100 AND 107
   cust_num     order_num    service_num
   101          300
   102          200            1
   102          200            2
   102          200            3
   105          250           10
   105          250           11
   105          251
   105          305
   106          210
   106          211            4
   106          211            5
   106          211            6
   106          211            7

Take into account all the customers in the CUSTOMERS table, regardless of whetherthey have placed orders or not. Orders are included in the derived table even if they are not yet associated with a service.

SELECT c.cust_num, o.order_num, s.service_num
   FROM (customers c LEFT OUTER JOIN orders o ON c.cust_num = o.cust_num)
                     LEFT OUTER JOIN service s ON o.order_num = s.order_num
   WHERE c.cust_num BETWEEN 100 AND 107

CUSTOMERS is the dominant table in the outer join that is enclosed in parentheses. The expression in parentheses is the dominant table of the outermost outer join. The NULL value is entered for missing item and service numbers.

cust_num     order_num    service_num
100
101          300
102          200            1
102          200            2
102          200            3
103
104
105          250           10
105          250           11
105          251
105          305
106          211            4
106          211            5
106          211            6
106          211            7
106          210  
107

The following examples refer to the CUSTOMERS and ORDERS tables. In order to better illustrate the possibilities of an outer join, orders without customers are also permitted. This means that the foreign key definition for the ORDERS table is ignored here. We shall assume that an order with the number 400 is in the ORDERS table and is not yet associated with a customer.

Select customer names and the associated order numbers from the CUSTOMERS and ORDERS tables and include customers who have not currently placed an order.
SELECT customers.company, orders.order_num FROM customers
LEFT OUTER JOIN orders ON customers.cust_num=orders.cust_num

Customers who have not placed an order, like Freddy’s Fishery with the customer number 104, are included in the derived table. The NULL value is entered for the missing order number.
```
 company                   order_num
 Siemens AG
 Login GmbH                300
 JIKO Gmbh                 200
 Plenzer Trading
 Freddy's Fishery
 The Poodle Parlor         250
 The Poodle Parlor         251
 The Poodle Parlor         305
 Foreign Ltd.              210
 Foreign Ltd.              211
 Externa & Co KG
```
Select customer names and order numbers from the CUSTOMERS and ORDERS tables and include orders that are not associated with a customer.
SELECT customers.company, orders.order_num FROM customers
RIGHT OUTER JOIN orders ON customers.cust_num=orders.cust_num

The order number 400 is also included in the derived table. The NULL value is entered for the missing customer name.
```
company              order_num
 JIKO Gmbh             200
 Foreign Ltd.          210
 Foreign Ltd.          211
 The Poodle Parlor     250
 The Poodle Parlor     251
 Login GmbH            300
 The Poodle Parlor     305
                       400
```
Select customer names and the associated order numbers from the CUSTOMERS and ORDERS tables while taking customers without orders and orders without customers into account.
SELECT customers.company, orders.order_num FROM customers
FULL OUTER JOIN orders ON customers.cust_num=orders.cust_num

A fictitious order with the order number 400, which is not yet associated with a customer, is also included in the derived tables, as is the customer Freddy’s Fishery who has not currently placed an order. NULL values are entered in place of the missing column values.
```
 company                   order_num
 Siemens AG
 Login GmbH                300
 JIKO Gmbh                 200
 Plenzer Trading
 Freddy's Fishery
 The Poodle Parlor         250
 The Poodle Parlor         251
 The Poodle Parlor         305
 Foreign Ltd.              210
 Foreign Ltd.              211
 Externa & Co KG
                           400
```

Your Browser is not longer supported

Please use Google Chrome, Mozilla Firefox or Microsoft Edge to view the page correctly

Compound joins