SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  4. 4.  Views
  1. 1.  Best Practices for Readable and Maintainable SQL Code
  2. 2.  Writing Efficient SQL Queries
  3. 3.  Understanding Query Optimization Methods
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Lesson 5.5: FULL OUTER JOIN - Combining Everything from Both Tables

The FULL OUTER JOIN is the most inclusive type of join. It returns all rows when there is a match in either the left or the right table. It is essentially a combination of a LEFT JOIN and a RIGHT JOIN.

What is a FULL OUTER JOIN?

A FULL OUTER JOIN creates a result set that includes all records from both tables.

  • If a row matches, columns from both tables are populated.
  • If there is a row in the left table with no match in the right, the right columns are NULL.
  • If there is a row in the right table with no match in the left, the left columns are NULL.

Visualization:

   Table A (potential_leads)    Table B (active_clients)
   +----+----------+            +----+----------+
   | id | name     |            | id | status   |
   +----+----------+            +----+----------+
   | 1  | Alice    | <--------> | 1  | Active   | (Match!)
   | 2  | Bob      | <--------? | NULL          | (Lead only, no account yet)
   | NULL          | <--------> | 3  | Active   | (Client only, not in lead list)
   +----+----------+            +----+----------+

FULL OUTER JOIN Syntax

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

Important Note on Database Support: Not all database systems support FULL OUTER JOIN natively.

  • PostgreSQL, SQL Server, and Oracle support it.
  • MySQL and MariaDB do NOT support it.

Workaround for MySQL/MariaDB

Since MySQL doesn't have FULL OUTER JOIN, developers achieve the same result by combining a LEFT JOIN and a RIGHT JOIN using the UNION operator:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

Practical Example

Imagine we are merging data from two different branch offices. Branch A has their own list of customers, and Branch B has theirs. We want a complete list of all customers across both branches, showing where they overlap.

SELECT
    a.customer_name AS branch_a_name,
    b.customer_name AS branch_b_name
FROM
    branch_a_customers AS a
FULL OUTER JOIN
    branch_b_customers AS b ON a.customer_id = b.customer_id;

Key Takeaways from This Lesson

  • FULL OUTER JOIN returns all records from both tables.
  • It uses NULLs to fill in the gaps where no match is found on either side.
  • It is the best tool for database synchronization and finding discrepancies between two lists.
  • If your database doesn't support it (like MySQL), use a UNION of a LEFT and RIGHT join.