Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL FULL JOIN

The `FULL JOIN` in PostgreSQL combines the results of both `LEFT JOIN` and `RIGHT JOIN`. It returns all records when there is a match in either the left or right table records, and fills in `NULLs` where there is no match.

Usage

The `FULL JOIN` is used when you want to retrieve all records from two tables and include rows with no direct matches in either table. It is particularly useful for datasets with records that may not have corresponding matches in the other table.

sql
SELECT columns
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;

In this syntax, `FULL JOIN` merges rows from `table1` and `table2` based on a shared column, including unmatched rows from both tables.

Examples

1. Basic FULL JOIN

sql
SELECT *
FROM employees
FULL JOIN departments ON employees.dept_id = departments.id;

This example retrieves all records from both the `employees` and `departments` tables, matching them on `dept_id` and `id`, and fills in `NULLs` for unmatched rows.

2. Using FULL JOIN with WHERE

sql
SELECT employees.name, departments.name
FROM employees
FULL JOIN departments ON employees.dept_id = departments.id
WHERE departments.name IS NOT NULL;

This query combines `FULL JOIN` with a `WHERE` clause to filter out results where the department name is `NULL`, showcasing how to refine results post-join.

3. FULL JOIN with Aggregation

sql
SELECT employees.dept_id, COUNT(employees.id) AS employee_count, departments.name
FROM employees
FULL JOIN departments ON employees.dept_id = departments.id
GROUP BY employees.dept_id, departments.name;

This example uses `FULL JOIN` with `GROUP BY` to aggregate data, providing a count of employees per department, including departments with no employees.

4. Handling NULL Values

sql
SELECT COALESCE(employees.name, 'No Employee') AS employee_name, 
       COALESCE(departments.name, 'No Department') AS department_name
FROM employees
FULL JOIN departments ON employees.dept_id = departments.id;

This example demonstrates handling `NULL` values by using `COALESCE` to provide default values for unmatched rows.

Comparison of JOIN Types

  • LEFT JOIN: Returns all records from the left table and matched records from the right table. Unmatched records from the right table are filled with `NULL`.
  • RIGHT JOIN: Returns all records from the right table and matched records from the left table. Unmatched records from the left table are filled with `NULL`.
  • FULL JOIN: Returns all records from both tables, filling `NULL` for unmatched rows from either table.

Tips and Best Practices

  • Use cautiously with large datasets. `FULL JOIN` can produce large result sets, so ensure it’s necessary for your analysis.
  • Handle `NULL` values. Be prepared to manage `NULL` values in your logic, especially when performing calculations or filtering results.
  • Optimize with indexes. Ensure appropriate indexing on join columns to improve performance.
  • Combine with other filters. Use `WHERE` clauses to filter results further and reduce result set size.
  • Test on subsets. Validate logic on smaller data samples before applying `FULL JOIN` to entire tables.
  • Performance Considerations. Be aware that `FULL JOIN` on very large tables can lead to significant processing time and resource usage.
  • Consider Alternatives. In some cases, using `LEFT JOIN` or `RIGHT JOIN` may be more appropriate depending on the specific requirements of your query.