The PostgreSQL database supports three major types of Outer joins:
- Left Outer Join or Left Join
- Right Outer Join or Right Join
- Full Outer Join or Full Join
Left Outer Join:
The Left Outer Join query after joining returns all the records from the Left table for the specified fields along with the records from the Right table where the join condition is met.
SELECT expr_1, expr_2, ... expr_n FROM table_1 LEFT OUTER JOIN table_2 ON join_predicate;
join_predicate: It is used to specify the joining conditions to be strictly followed for joining.
ID STATE RATE 1 A 60 2 B 70 3 C 65 4 D 80 5 E 78
ID NAME PERCENT 1 IT 60 2 Sales 75 3 Bank 50
SELECT “EMPLOYMENT”.“ID”, “EMPLOYMENT”.“STATE”, “DEPARTMENT”.“NAME” FROM “EMPLOYMENT” LEFT OUTER JOIN “DEPARTMENT” ON “EMPLOYMENT”.“ID” = “DEPARTMENT”.“ID”;
ID STATE NAME 1 A IT 2 B Sales 3 C Bank 4 D 5 E
The EMPLOYMENT and the DEPARTMENT are the already existing tables that are joined with LEFT OUTER JOIN query so that all the records from the EMPLOYMENT table for the specified fields along with the records from the DEPARTMENT table where the join condition is met is combined in the result set.