Another type of join is the outer join. It is created by using the keyword LEFT, RIGHT or FULL in the join expression. Unlike an inner join, the following applies to an outer join:
There are one (LEFT, RIGHT) or two (FULL) dominant tables. If a row in a dominant table does not satisfy the join condition, the row is nevertheless included in the derived table. The derived column that references the other table is set to NULL values.
Example
As in the first join example, select customer names and the associated order numbers from the CUSTOMERS and ORDERS tables. In this case, however, list all customers, even those who have not yet placed an order. To do this, you create the following outer join:
SELECT company, 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 now 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