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.
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:
SELECT item.item_no,item_descrip, invoice.invoice_no,invoice.sold_qty FROM item LEFT JOIN invoice ON item.item_no=invoice.item_no;
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;
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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework