SQL FULL JOIN

Learn how to combine all records from two tables using SQL FULL JOIN and understand how unmatched rows are handled.

What is FULL JOIN?

A FULL JOIN, also called FULL OUTER JOIN, returns all matching and non-matching rows from both tables. When a row has no matching record in the other table, the missing columns contain NULL.

In other words, FULL JOIN combines the behavior of LEFT JOIN and RIGHT JOIN.

Important: FULL JOIN in SQLite

Modern versions of SQLite support FULL OUTER JOIN. However, to make examples reliable across SQLite environments, the examples below use UNION with LEFT JOIN to reproduce FULL JOIN behavior.

Basic FULL JOIN Example

Here, every product from both tables is included. Matching products are combined, while products appearing in only one table contain NULL values for the other table.

CREATE TABLE online_products (
  product_id INTEGER,
  product_name TEXT
);

CREATE TABLE store_products (
  product_id INTEGER,
  product_name TEXT
);

INSERT INTO online_products VALUES
  (1, 'Laptop'),
  (2, 'Tablet'),
  (3, 'Camera');

INSERT INTO store_products VALUES
  (2, 'Tablet'),
  (3, 'Camera'),
  (4, 'Smartwatch');

SELECT
  o.product_id AS online_id,
  o.product_name AS online_product,
  s.product_id AS store_id,
  s.product_name AS store_product
FROM online_products AS o
LEFT JOIN store_products AS s
  ON o.product_id = s.product_id

UNION

SELECT
  o.product_id AS online_id,
  o.product_name AS online_product,
  s.product_id AS store_id,
  s.product_name AS store_product
FROM store_products AS s
LEFT JOIN online_products AS o
  ON s.product_id = o.product_id
WHERE o.product_id IS NULL

ORDER BY online_id, store_id;

FULL JOIN with Employee Records

FULL JOIN is useful when two systems contain employee records that may not completely match.

CREATE TABLE company_a (
  employee_id INTEGER,
  employee_name TEXT
);

CREATE TABLE company_b (
  employee_id INTEGER,
  employee_name TEXT
);

INSERT INTO company_a VALUES
  (101, 'Aarav'),
  (102, 'Meera'),
  (103, 'Kabir');

INSERT INTO company_b VALUES
  (102, 'Meera'),
  (103, 'Kabir'),
  (104, 'Riya');

SELECT
  a.employee_id AS company_a_id,
  a.employee_name AS company_a_name,
  b.employee_id AS company_b_id,
  b.employee_name AS company_b_name
FROM company_a AS a
LEFT JOIN company_b AS b
  ON a.employee_id = b.employee_id

UNION

SELECT
  a.employee_id AS company_a_id,
  a.employee_name AS company_a_name,
  b.employee_id AS company_b_id,
  b.employee_name AS company_b_name
FROM company_b AS b
LEFT JOIN company_a AS a
  ON b.employee_id = a.employee_id
WHERE a.employee_id IS NULL

ORDER BY company_a_id, company_b_id;

Finding Unmatched Records

FULL JOIN can help identify records that exist in one table but not the other. In this example, the result is filtered to show only unmatched customers.

CREATE TABLE website_customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE app_customers (
  customer_id INTEGER,
  customer_name TEXT
);

INSERT INTO website_customers VALUES
  (1, 'Nisha'),
  (2, 'Rohan'),
  (3, 'Tara');

INSERT INTO app_customers VALUES
  (2, 'Rohan'),
  (3, 'Tara'),
  (4, 'Dev');

SELECT
  w.customer_id AS website_id,
  w.customer_name AS website_name,
  a.customer_id AS app_id,
  a.customer_name AS app_name
FROM website_customers AS w
LEFT JOIN app_customers AS a
  ON w.customer_id = a.customer_id
WHERE a.customer_id IS NULL

UNION

SELECT
  w.customer_id AS website_id,
  w.customer_name AS website_name,
  a.customer_id AS app_id,
  a.customer_name AS app_name
FROM app_customers AS a
LEFT JOIN website_customers AS w
  ON a.customer_id = w.customer_id
WHERE w.customer_id IS NULL

ORDER BY website_id, app_id;

FULL JOIN with Different Data

The two tables do not need to contain the same number of records. FULL JOIN keeps records from both sides.

CREATE TABLE morning_classes (
  class_id INTEGER,
  class_name TEXT
);

CREATE TABLE evening_classes (
  class_id INTEGER,
  class_name TEXT
);

INSERT INTO morning_classes VALUES
  (1, 'Physics'),
  (2, 'Chemistry'),
  (3, 'Biology');

INSERT INTO evening_classes VALUES
  (2, 'Chemistry'),
  (4, 'History'),
  (5, 'Geography');

SELECT
  m.class_id AS morning_id,
  m.class_name AS morning_class,
  e.class_id AS evening_id,
  e.class_name AS evening_class
FROM morning_classes AS m
LEFT JOIN evening_classes AS e
  ON m.class_id = e.class_id

UNION

SELECT
  m.class_id AS morning_id,
  m.class_name AS morning_class,
  e.class_id AS evening_id,
  e.class_name AS evening_class
FROM evening_classes AS e
LEFT JOIN morning_classes AS m
  ON e.class_id = m.class_id
WHERE m.class_id IS NULL

ORDER BY morning_id, evening_id;

FULL JOIN with Sales Data

FULL JOIN can compare data from two sources and show records that exist in either source.

CREATE TABLE january_sales (
  product_id INTEGER,
  product_name TEXT,
  amount INTEGER
);

CREATE TABLE february_sales (
  product_id INTEGER,
  product_name TEXT,
  amount INTEGER
);

INSERT INTO january_sales VALUES
  (1, 'Keyboard', 1200),
  (2, 'Mouse', 800),
  (3, 'Monitor', 2500);

INSERT INTO february_sales VALUES
  (2, 'Mouse', 950),
  (3, 'Monitor', 2800),
  (4, 'Webcam', 1100);

SELECT
  j.product_name AS january_product,
  j.amount AS january_amount,
  f.product_name AS february_product,
  f.amount AS february_amount
FROM january_sales AS j
LEFT JOIN february_sales AS f
  ON j.product_id = f.product_id

UNION

SELECT
  j.product_name AS january_product,
  j.amount AS january_amount,
  f.product_name AS february_product,
  f.amount AS february_amount
FROM february_sales AS f
LEFT JOIN january_sales AS j
  ON f.product_id = j.product_id
WHERE j.product_id IS NULL

ORDER BY january_product, february_product;

FULL JOIN Using SQLite’s FULL OUTER JOIN

If your SQLite environment supports modern FULL OUTER JOIN syntax, you can write the query directly using FULL OUTER JOIN.

CREATE TABLE left_items (
  item_id INTEGER,
  item_name TEXT
);

CREATE TABLE right_items (
  item_id INTEGER,
  item_name TEXT
);

INSERT INTO left_items VALUES
  (1, 'Notebook'),
  (2, 'Pen'),
  (3, 'Folder');

INSERT INTO right_items VALUES
  (2, 'Pen'),
  (3, 'Folder'),
  (4, 'Marker');

SELECT
  l.item_id AS left_id,
  l.item_name AS left_item,
  r.item_id AS right_id,
  r.item_name AS right_item
FROM left_items AS l
FULL OUTER JOIN right_items AS r
  ON l.item_id = r.item_id
ORDER BY l.item_id, r.item_id;

FULL JOIN vs Other JOINs

JOIN Type	Matching Rows	Unmatched Left Rows	Unmatched Right Rows
INNER JOIN	Yes	No	No
LEFT JOIN	Yes	Yes	No
RIGHT JOIN	Yes	No	Yes
FULL JOIN	Yes	Yes	Yes

Common Uses of FULL JOIN

Compare records from two different data sources.
Find records missing from either table.
Combine complete datasets while preserving unmatched rows.
Compare customer, product, employee, or sales databases.
Identify differences between two versions of a dataset.

Best Practices

Use a reliable column for matching records in the ON clause.
Use IS NULL to identify records missing from one side.
Use table aliases to make FULL JOIN queries easier to read.
Use COALESCE() when you want to replace NULL values.
When supporting SQLite environments where FULL OUTER JOIN may not be available, use LEFT JOIN with UNION.

The FULL JOIN is useful when you need every record from both tables. Matching records are combined, while records that exist on only one side are still included with NULL values for the missing side.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.