PostgreSQL LEFT JOIN or LEFT OUTER JOIN

What is PostgreSQL Left Join or Left Outer Join?

The PostgreSQL LEFT JOIN joins two tables and fetches rows based on a condition, which is matching in both tables and the unmatched rows will also be available from the table written before the JOIN clause.

Suppose: table1 LEFT JOIN table2 JOIN CONDITION

In PostgreSQL LEFT JOIN, table table2 depends on table table1 and all tables on which table1 depends and also table table1 depends on all tables that are used in the LEFT JOIN condition except table2. The LEFT JOIN condition is used to decide how to retrieve rows from table table2.

If there is a row in table1 that matches the WHERE clause, but there is no row in table2 that matches the ON condition, an extra table2 row is generated with all columns set to NULL.

So, in case of LEFT JOIN or LEFT OUTER JOIN, PostgreSQL -

1. takes all selected values from the left table

2. combines them with the column names ( specified in the condition ) from the right table

3. retrieve the matching rows from both the associated tables.

4. sets the value of every column from the right table to NULL which is unmatched with the left table.

Syntax:

Select *
FROM table1
LEFT [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;

Pictorial Presentation of PostgreSQL Left Join or Left Outer Join

PostgreSQL LEFT join fetches a complete set of records from the left, with the matching records (depending on the availability) in right. The result is NULL in the right side when no matching will take place.

Sample table: Customer:

Sample table: Item:

Sample table: Invoice:

Example:

Code:

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT JOIN invoice
ON item.item_no=invoice.item_no;

OR

Code:

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT OUTER JOIN invoice
ON item.item_no=invoice.item_no;

Output:

Explanation :

In the above example, the item_no I8 of item table not exists in the invoice table, and for this rows of item table a new row in invoice table have generated and sets the NULL for this rows.

Pictorial Presentation for the above example

Previous: INNER JOIN
Next: RIGHT JOIN