[MySQL, Oracle] FULL JOIN and Special Joins (SELF JOIN, NATURAL JOIN, SEMI JOIN, ANTI JOIN)

FULL (OUTER) JOIN

Provides a JOIN that includes all rows from both tables, filling unmatched rows with NULL values.

Features

• Union of the two tables
• Not supported in MySQL; must be implemented manually
  (Supported in PostgreSQL, Oracle, SQL Server)
• Rows existing only in one table have the corresponding columns set to NULL

Example

Table data example
customer: (id=1, name=‘Dave’), (id=2, name=‘Jane’), (id=3, name=‘Lee’)
orders: (cust_id=1, order_date=‘2025–01–01’), (cust_id=2, order_date=‘2025–01–02’), (cust_id=4, order_date=‘2025–01–03’)

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  FULL OUTER JOIN orders o 
    ON c.id = o.cust_id;

Result

1 | Dave | 2025–01–01
2 | Jane | 2025–01–02
3 | Lee | NULL
NULL | NULL | 2025–01–03

Implementing FULL JOIN in MySQL

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  LEFT JOIN orders o 
    ON c.id = o.cust_id

UNION

SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
 RIGHT JOIN orders o
    ON c.id = o.cust_id

However, note that the above method requires two JOIN operations.

Performance Improvement Methods

While there is no method to significantly boost performance, improvements can be made as follows.

1. Exclusive Union using Subqueries (Performance Optimization) — Performance-Focused

-- All left data + intersection
SELECT 
       c.id AS cust_id
     , c.name
     , o.order_date
  FROM customer c
  LEFT JOIN orders o 
    ON c.id = o.cust_id

UNION ALL -- Use UNION ALL without duplicate checks to reduce cost

-- Data present only in the right table (excluding intersection)
SELECT 
       o.cust_id
     , o.name
     , o.order_date
  FROM customer c
 RIGHT JOIN orders o
    ON c.id = o.cust_id
 WHERE c.id IS NULL; -- Excludes data already retrieved above

2. Method using aggregate functions — Reduces computation when multiple tables are involved

SELECT 
       cust_id
     , MAX(name) AS name
     , MAX(order_date) AS order_date
FROM (
    -- Prepare customer table data
    SELECT id AS cust_id
         , name
         , NULL AS order_date 
    FROM customer
    
    UNION ALL
    
    -- Prepare orders table data
    SELECT cust_id
         , NULL AS name
         , order_date 
    FROM orders)
 AS combined
GROUP BY cust_id;

SELF JOIN

Useful for hierarchical structures or self-referential data by joining with itself

Features

• Assigns different aliases to the table and connects via a common column

connects via a common column
SELECT e1.name
     , e2.name 
 FROM employees e1
 JOIN employees e2
   ON e1.manager_id = e2.id;

NATURAL JOIN

Automatically finds columns with the same name and data type in two tables for an equi JOIN

Get leedohyung28’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Features

Connects without a separate ON condition if columns share the same name

SELECT *
  FROM customers NATURAL JOIN cities;

SEMI JOIN

Returns only rows from the left table that exist in the right table, without including the right table’s columns in the result.

Features

• Used to check if matching data exists rather than performing an actual join
• Uses EXISTS or IN operators

-- Using EXISTS
SELECT *
  FROM Customers c
 WHERE EXISTS (
                SELECT 1
                 FROM Orders o
                WHERE o.customer_id = c.id
              );

-- Using IN
SELECT *
  FROM Customers
  WHERE id IN (
                  SELECT customer_id
                  FROM Orders
              );

vs. INNER JOIN

Example: If one customer places 100 orders

• INNER JOIN: The same customer name appears 100 times in the result. (Requires DISTINCT to remove duplicates)
• SEMI JOIN: The customer name appears only once in the result.

Purpose of SEMI JOIN

• Existence Check : When you need a list of subjects meeting specific conditions but don’t care about detailed records.
• Performance Optimization : Often faster than using INNER JOIN + DISTINCT.
• Data integrity check : When you want to filter only the dataset satisfying specific criteria.

ANTI JOIN

A JOIN technique that returns only data from the left table where no matching rows exist in the right table.

<=> SEMI JOIN

Features

• Extracting the difference set : Equivalent to finding the difference between two sets.
• Verifying Absence : Used when filtering targets lacking specific records.
• Duplicate removal effect
• Typically uses NOT EXISTS or NOT IN operators.

-- Using NOT EXISTS
SELECT *
  FROM Customers c
 WHERE NOT EXISTS (
                    SELECT 1 
                      FROM Orders o 
                     WHERE o.customer_id = c.id
                  );

-- Using NOT IN
SELECT *
  FROM Customers
  WHERE id NOT IN (
                    SELECT customer_id
                      FROM Orders
                  );

-- LEFT JOIN + NULL check
SELECT c.*
  FROM Customers c
  LEFT JOIN Orders o
    ON c.id = o.customer_id
 WHERE o.customer_id IS NULL;

Using NOT IN may omit entire results if the right table contains NULL values.