Example of FULL OUTER JOIN
-- Creating employees table
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name TEXT,
department_id INTEGER,
salary INTEGER
);
-- Creating departments table
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name TEXT
);
As we can saw in the image that the we have created two employees and departments table.
After Inserting Some records into the employees table, the table looks:
After Inserting Some records into the departments table, the table looks:
Example 1: Basic FULL OUTER JOIN
SELECT * FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Output:
Output Explanation: In this example, the querry we have written here fethes all records from both tables and matches them based on the “department_id“. The result includes all rows from the ‘employees‘ table and the ‘departments‘ table, with unmatched records filled with NULL values.
Example 2: Filtering with WHERE Clause
SELECT employees.employee_id, employees.name, COALESCE(departments.department_name, 'No Department') AS department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Output:
Output Explanation: In this example, the query is applying a filter to the result set using the WHERE clause. It selects only those records where the employee’s salary is greater than $50,000.
Here, the FULL OUTER JOIN ensures that even if an employee does not belong to any department or if a department has no employees that meets the criteria, their information is still included in the result.
Example 3: Handling NULL Values with COALESCE
SELECT employees.employee_id, employees.name, COALESCE(departments.department_name, 'No Department') AS department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Output:
Output Explanation: In this example, we have used ‘COALESCE‘ function to handle NULL values. If we can see in the previous two examples, we are getting the output with the Null values. But in this example, we did not get any null values and this happened becasue of the ‘COALESCE‘ function only.
It selcts the employee ID, name, and department name. If an employee does not belong to any department, the ‘COALESCE‘ function will simply replace the NULL department name with ‘No Department,’ and it also ensures a more user-friendly presentation of the data.
FULL OUTER JOIN in SQLite
In the area of data querying and manipulation, the ability to combine information from different sources is important. SQLite, a popular embedded database engine, offers a range of join operations to fast process. One such operation FULL OUTER
JOIN
is particularly powerful as it allows us to merge data from two tables, even if there are no direct matches between them. In this article, we will explore the concept of FULL OUTER JOIN in
SQLite, its syntax, and how it can be used to extract valuable insights from your data.