The Project | About Us | Contribute | Donations | License

HOME

Introduction

In SQL, the FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. This means that it combines the results of both LEFT JOIN and RIGHT JOIN. If there is no match, the result is NULL on the side that does not have a match.

Key Concepts

FULL OUTER JOIN: Combines the results of both LEFT JOIN and RIGHT JOIN.
NULL Values: When there is no match, the result will contain NULL values for columns from the table that does not have a match.

Syntax

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Practical Example

Tables Setup

Let's consider two tables, employees and departments.

employees | employee_id | employee_name | department_id | |-------------|----------------|---------------| | 1 | Alice | 10 | | 2 | Bob | 20 | | 3 | Charlie | 30 |

departments | department_id | department_name | |---------------|-----------------| | 10 | HR | | 20 | IT | | 40 | Marketing |

Example Query

We want to list all employees and their corresponding departments, including departments that do not have any employees and employees that do not belong to any department.

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

Result

employee_id	employee_name	department_name
1	Alice	HR
2	Bob	IT
3	Charlie	NULL
NULL	NULL	Marketing

Explanation

Alice and Bob have matching departments, so their department names are displayed.
Charlie does not have a matching department, so NULL is displayed in the department_name column.
The Marketing department does not have any employees, so NULL is displayed in the employee_id and employee_name columns.

Practical Exercises

Exercise 1

Task: Write a query to list all products and their corresponding categories, including categories that do not have any products and products that do not belong to any category.

Tables: products | product_id | product_name | category_id | |------------|---------------|-------------| | 1 | Laptop | 1 | | 2 | Mouse | 2 | | 3 | Keyboard | NULL |

categories | category_id | category_name | |-------------|---------------| | 1 | Electronics | | 2 | Accessories | | 3 | Furniture |

Expected Result: | product_id | product_name | category_name | |------------|---------------|---------------| | 1 | Laptop | Electronics | | 2 | Mouse | Accessories | | 3 | Keyboard | NULL | | NULL | NULL | Furniture |

Solution:

SELECT products.product_id, products.product_name, categories.category_name
FROM products
FULL OUTER JOIN categories
ON products.category_id = categories.category_id;

Exercise 2

Task: Write a query to list all students and their corresponding courses, including courses that do not have any students and students that are not enrolled in any course.

Tables: students | student_id | student_name | course_id | |------------|---------------|-----------| | 1 | John | 101 | | 2 | Jane | 102 | | 3 | Mike | NULL |

courses | course_id | course_name | |-----------|---------------| | 101 | Mathematics | | 102 | Science | | 103 | History |

Expected Result: | student_id | student_name | course_name | |------------|---------------|--------------| | 1 | John | Mathematics | | 2 | Jane | Science | | 3 | Mike | NULL | | NULL | NULL | History |

Solution:

SELECT students.student_id, students.student_name, courses.course_name
FROM students
FULL OUTER JOIN courses
ON students.course_id = courses.course_id;

Common Mistakes and Tips

Common Mistake: Forgetting to handle NULL values in the result set.
- Tip: Always check for NULL values in your result set and handle them appropriately in your application logic.
Common Mistake: Misunderstanding the difference between FULL OUTER JOIN and other types of joins.
- Tip: Remember that FULL OUTER JOIN combines the results of both LEFT JOIN and RIGHT JOIN.

Conclusion

In this section, we learned about the FULL OUTER JOIN operation in SQL, which allows us to combine the results of both LEFT JOIN and RIGHT JOIN. We explored its syntax, practical examples, and exercises to reinforce the concept. Understanding FULL OUTER JOIN is crucial for working with complex queries involving multiple tables, ensuring that no data is left out from either table.

FULL OUTER JOIN

Introduction

Key Concepts

Syntax

Practical Example

Tables Setup

Example Query

Result

Explanation

Practical Exercises

Exercise 1

Exercise 2

Common Mistakes and Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project