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 calledLEFT JOIN
)The PostgreSQL
RIGHT OUTER JOIN
(or sometimes calledRIGHT JOIN
)The PostgreSQL
FULL OUTER JOIN
(or sometimes calledFULL 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.