Handling NULL in join condition

In SQL, the behavior of joins involving NULL values in the join conditions depends on the type of join used. Here’s a breakdown:

1. INNER JOIN

Behavior: Rows with NULL values in the join condition are excluded from the result.

Explanation: This is because NULL = NULL evaluates to FALSE, and an inner join only includes rows where the join condition evaluates to TRUE.

Example:

SELECT * FROM TableA INNER JOIN TableB ON TableA.column1 = TableB.column2;

If TableA.column1 or TableB.column2 contains NULL, those rows will not be included.

2. LEFT JOIN (or LEFT OUTER JOIN)

Behavior: Rows from the left table are included in the result regardless of whether the join condition evaluates to TRUE or contains NULL. If the join condition fails due to a NULL in the right table, the result will include NULL values for columns from the right table.

Example:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.column1 = TableB.column2; 

If TableB.column2 contains NULL, rows from TableA will still be included, with NULL values for TableB columns.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Behavior: Similar to the LEFT JOIN, but ensures all rows from the right table are included, even if the join condition involves NULL.

Example:

SELECT * FROM TableA RIGHT JOIN TableB ON TableA.column1 = TableB.column2; 

If TableA.column1 contains NULL, rows from TableB will still be included, with NULL values for TableA columns

4. FULL OUTER JOIN

Behavior: Combines the behaviors of LEFT JOIN and RIGHT JOIN. Rows from both tables are included regardless of whether the join condition involves NULL.

Example:

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.column1 = TableB.column2; 

Rows with NULL values in TableA.column1 or TableB.column2 will be included in the result, with NULL for unmatched columns.

5. CROSS JOIN

Behavior: A cross join produces the Cartesian product of the two tables. NULL values do not affect the result since no join condition is used.

Example:

SELECT * FROM TableA CROSS JOIN TableB;

6. SELF JOIN

• Behavior: Acts like an INNER JOIN or OUTER JOIN (depending on the type) but is applied within the same table. NULL values are treated the same way as in other joins.

Key Takeaways

• NULL in equality conditions (=): Evaluates to FALSE.
• NULL in non-equality conditions (<>, >, <): Evaluates to FALSE.
• Use functions like IS NULL or IS NOT NULL to handle NULL explicitly in join conditions, if needed.
  • SELECT * FROM TableA LEFT JOIN TableB ON TableA.column1 = TableB.column2 OR TableB.column2 IS NULL;

If you want to match rows where the department_id is NULL in both the employee and department tables, you cannot use the standard = operator, as NULL = NULL evaluates to FALSE. Instead, you need to explicitly handle the NULL values in your join condition using the IS NULL operator.

Query to Handle This Scenario:

SELECT e.employee_id, e.employee_name, d.department_id, d.department_name
FROM employee e
LEFT JOIN department d
ON (e.department_id = d.department_id OR (e.department_id IS NULL AND d.department_id IS NULL));

Explanation:

1. e.department_id = d.department_id:
   • Matches rows where the department_id values are not NULL and equal in both tables.
2. (e.department_id IS NULL AND d.department_id IS NULL):
   • Explicitly handles the case where both department_id values are NULL, allowing them to match.
3. LEFT JOIN:
   • Ensures that all employees are included in the result, even if they do not have a corresponding department.

Example Data:

employee Table:

department Table:

Output of the Query:

Alternative with FULL OUTER JOIN (If Both Tables Must Be Fully Mapped):

If you want to ensure no rows are missed from either table, you can use a FULL OUTER JOIN:

SELECT e.employee_id, e.employee_name, d.department_id, d.department_name
FROM employee e
FULL OUTER JOIN department d
ON (e.department_id = d.department_id OR (e.department_id IS NULL AND d.department_id IS NULL));

This approach guarantees that all rows from both tables are included, handling NULL matches appropriately.