Using the Outer join

Another class of join is known as the OUTER JOIN. In OUTER JOIN, the results might contain both matched and unmatched rows. It is for this reason that beginners might find such joins a little confusing. However, the logic is really quite straightforward.

The following are the three types of Outer joins:

• The PostgreSQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• The PostgreSQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• The PostgreSQL FULL OUTER JOIN (or sometimes called FULL JOIN)

Left outer join

Left outer join returns all rows from the left-hand table specified in the ON condition, and only those rows from the other tables where the joined fields are equal (the join condition is met). If the condition is not met, the values of the columns in the second table are replaced by null values.

The syntax for the PostgreSQL LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON condition1, condition2

In the case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table1 that does not satisfy the join condition with any row in table2, a joined row is added with null values in the columns of table2. Thus, the joined table always has at least one row for each row in table1.

Let's consider an example where you want to fetch the order details placed by a customer. Now, there can be a scenario where a customer doesn't have any order placed that is open, and the order table contains only those orders that are open. In this case, we will use a left outer join to get information on all the customers and their corresponding orders:

SELECT customer.customer_id, customer.customer_name, orders.order_number
FROM customer
LEFT OUTER JOIN orders
ON customer.customer_id = orders.customer_id

This LEFT OUTER JOIN example will return all rows from the customer table and only those rows from the orders table where the join condition is met.

If a customer_id value in the customer table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

Right outer join

Another type of join is called a PostgreSQL RIGHT OUTER JOIN. This type of join returns all rows from the right-hand table specified in the ON condition, and only those rows from the other table where the joined fields are equal (join condition is met). If the condition is not met, the value of the columns in the first table is replaced by null values.

The syntax for the PostgreSQL RIGHT OUTER JOIN is as follows:

SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;
Condition1, condition2;

In the case of RIGHT OUTER JOIN, an inner join is performed first. Then, for each row in table2 that does not satisfy the join condition with any row in table1, a joined row is added with null values in the columns of table1. This is the converse of a left join; the result table will always have a row for each row in table2.

Let's consider an example where you want to fetch the invoice information for the orders. Now, when an order is completed, we generate an invoice for the customer so that he can pay the amount. There can be a scenario where the order has not been completed, so the invoice is not generated yet. In this case, we will use a right outer to get all the orders information and corresponding invoice information.

SELECT invoice.invoice_id, invoice.invoice_date,  orders.order_number
FROM invoice
RIGHT OUTER JOIN orders
ON invoice.order_number= orders.order_number

This RIGHT OUTER JOIN example will return all rows from the order table and only those rows from the invoice table where the joined fields are equal. If an order_number value in the invoice table does not exist, all the fields in the invoice table will display as <null> in the result set.

Full outer join

Another type of join is called a PostgreSQL FULL OUTER JOIN. This type of join returns all rows from the left-hand table and right-hand table with nulls in place where the join condition is not met.

The syntax for the PostgreSQL FULL OUTER JOIN is as follows:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Condition1,condition2;

First, an inner join is performed. Then, for each row in table1 that does not satisfy the join condition with any row in table2, a joined row is added with null values in the columns of table2. Also, for each row of table2 that does not satisfy the join condition with any row in table1, a joined row with null values in the columns of table1 is added.

Let's consider an example where you want to fetch an invoice information and all the orders information. In this case, we will use a full outer to get all the orders information and the corresponding invoice information.

SELECT invoice.invoice_id, invoice.invoice_date, orders.order_number
FROM invoice
FULL  OUTER JOIN orders
ON invoice.order_number= orders.order_number;

This FULL OUTER JOIN example will return all rows from the invoice table and the orders table and, whenever the join condition is not met, <null> will be extended to those fields in the result set.

If an order_number value in the invoice table does not exist in the orders table, all the fields in the orders table will display as <null> in the result set. If order number in order's table does not exist in the invoice table, all fields in the invoice table will display as <null> in the result set.