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:
LEFT JOIN invoice
LEFT OUTER JOIN invoice
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
- Weekly Trends
- 100 Python Projects for Beginners with solution
- Mastering NumPy: 100 Exercises with solutions
- SQL Tutorial
- SQL Inner Join
- Python Exercises, Practice, Solution
- Python Interview Questions and Answers: Comprehensive Guide
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises