PostgreSQL FULL OUTER JOIN

PostgreSQL FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, with matching rows from both sides wherever available. If there is no match, the result will contain NULL columns from the table without a matching row.

• The key feature of FULL OUTER JOIN is that it includes all rows from both tables, regardless of whether a match exists between them.

1. Syntax of FULL OUTER JOIN

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

• table1.column and table2.column are the columns used to match the rows between the two tables.

2. Example 1: Simple FULL OUTER JOIN

Scenario:

Let’s assume we have two tables:

employees Table:

employee_id	name
1	Alice
2	Bob
3	Charlie

departments Table:

department_id	department_name
1	HR
2	IT
4	Marketing

Query: Get All Employees and Departments, Even if They Don’t Match

SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.employee_id = departments.department_id;

Result:

name	department_name
Alice	NULL
Bob	NULL
Charlie	NULL
NULL	HR
NULL	IT
NULL	Marketing

• Explanation:
  • The query matches rows based on employee_id (from employees) and department_id (from departments).
  • Since there are no matching employee_id and department_id, the result contains NULL for columns from the table without a match.

3. Example 2: FULL OUTER JOIN with Matching Rows

Scenario:

Consider the orders table:

orders Table:

order_id	product_name	customer_id
101	Laptop	1
102	Phone	2
103	Tablet	NULL

customers Table:

customer_id	customer_name
1	Alice
2	Bob
4	Charlie

Query: Get All Orders and Customers, Including Orders Without Customers

SELECT orders.order_id, orders.product_name, customers.customer_name
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

Result:

order_id	product_name	customer_name
101	Laptop	Alice
102	Phone	Bob
103	Tablet	NULL
NULL	NULL	Charlie

• Explanation:
  • The first two rows match orders.customer_id with customers.customer_id.
  • The third row (order_id = 103) doesn’t have a matching customer_id, so the result shows NULL for the customer_name.
  • The fourth row (customer_id = 4) has no matching order, so the result shows NULL for the order_id and product_name.

4. Example 3: FULL OUTER JOIN with Both Tables Having Non-Matching Rows

Scenario:

Consider the employees table:

employees Table:

employee_id	employee_name
1	Alice
2	Bob

projects Table:

project_id	project_name	employee_id
101	Project A	1
102	Project B	3

Query: Get All Employees and Projects, Even If There Is No Match

SELECT employees.employee_name, projects.project_name
FROM employees
FULL OUTER JOIN projects
ON employees.employee_id = projects.employee_id;

Result:

employee_name	project_name
Alice	Project A
Bob	NULL
NULL	Project B

• Explanation:
  • The first row matches employee_id = 1 with project_id = 101 for Alice.
  • The second row shows Bob with NULL for project_name because there is no project for Bob.
  • The third row shows NULL for employee_name and Project B because there is no employee assigned to that project.

5. Key Points to Remember

• Matching Rows: FULL OUTER JOIN returns rows where there is a match based on the specified column(s).
• Non-Matching Rows: It also returns rows that do not match from both the left and right tables, filling in NULL for missing columns.
• More Efficient than Multiple LEFT JOIN/RIGHT JOIN: When you need all rows from both tables, a FULL OUTER JOIN is more efficient than doing multiple LEFT JOIN and RIGHT JOIN queries.

6. Performance Considerations

• Complexity: FULL OUTER JOIN can be computationally expensive, especially when joining large tables, as it must find all possible matches between rows.
• Indexes: Consider indexing the columns used in the ON clause to optimize performance.

7. Summary of FULL OUTER JOIN

Join Type	Description
FULL OUTER JOIN	Returns all rows from both tables. If there is no match, NULL values are filled in for missing columns.
Use Case	Useful when you need all records from both tables, regardless of whether a match exists.
Performance	May be slower on large tables, as it needs to match and include all rows from both tables.

Would you like to dive into more advanced use cases with FULL OUTER JOIN, or see a complex example? 🚀