FULL OUTER JOIN and CROSS JOIN
Learn to preserve matched and unmatched rows from both tables with FULL OUTER JOIN and generate Cartesian products with CROSS JOIN for combinations, matrix generation, and test data.
In this lesson, you will learn
- ✓Combine matched and unmatched rows from both tables using FULL OUTER JOIN
- ✓Generate Cartesian products using CROSS JOIN
- ✓Identify matched, left-only, and right-only rows in a FULL OUTER JOIN result
- ✓Understand why unmatched columns contain NULL values
- ✓Use COALESCE to display a common identifier from either side
- ✓Apply CROSS JOIN to generate combinations of values
- ✓Estimate result size and avoid unintended Cartesian products
- ✓Choose the appropriate join type for reconciliation and reporting
1. Why Do We Need FULL OUTER JOIN and CROSS JOIN?
In previous lessons, you learned how INNER JOIN returns matching rows and how LEFT JOIN and RIGHT JOIN preserve unmatched rows from one side.
Two other joins solve different problems.
**FULL OUTER JOIN** preserves rows from both tables, whether they match or not.
**CROSS JOIN** produces every possible combination of rows from two tables, without requiring a matching condition.
These joins serve different purposes:
- Use FULL OUTER JOIN when comparing two datasets and keeping records that exist on either side.
- Use CROSS JOIN when you intentionally need every combination of two sets of values.
For example, a finance team might compare expected payments with received payments. A testing team might combine browser types with operating systems to generate test scenarios.
The correct join depends on which rows and combinations your task requires.
2. FULL OUTER JOIN: Preserve Rows from Both Tables
## Syntax
```sql
SELECT
a.column_name,
b.column_name
FROM table_a AS a
FULL OUTER JOIN table_b AS b
ON a.matching_column = b.matching_column;
```
PostgreSQL also accepts FULL JOIN as shorthand for FULL OUTER JOIN.
### Example tables
Suppose a company maintains two datasets.
**employees_current**
| employee_id | employee_name |
|---:|---|
| 1 | Asha |
| 2 | Rahul |
| 3 | Meera |
**employees_payroll**
| employee_id | salary |
|---:|---:|
| 1 | 50000 |
| 2 | 60000 |
| 4 | 45000 |
Employee IDs 1 and 2 exist in both tables. Employee 3 exists only in employees_current, while employee 4 exists only in employees_payroll.
### Query
```sql
SELECT
c.employee_id AS current_employee_id,
c.employee_name,
p.employee_id AS payroll_employee_id,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
ORDER BY COALESCE(c.employee_id, p.employee_id);
```
### Expected output
| current_employee_id | employee_name | payroll_employee_id | salary |
|---:|---|---:|---:|
| 1 | Asha | 1 | 50000 |
| 2 | Rahul | 2 | 60000 |
| 3 | Meera | NULL | NULL |
| NULL | NULL | 4 | 45000 |
### Understand the result
- ID 1 matches on both sides, so the row contains data from both tables.
- ID 2 also matches on both sides.
- ID 3 has no payroll match, but its current employee row remains.
- ID 4 has no current employee match, but its payroll row remains.
**Main rule:** FULL OUTER JOIN preserves all matched row combinations and all unmatched rows from both inputs.
Preserve matched and unmatched employee records
The result contains matching employees plus employees present in only one dataset. COALESCE provides a common sort key from whichever side has an employee ID.
3. Set Up the FULL OUTER JOIN Example
Run the following setup in a PostgreSQL practice database.
If these table names already exist in your database, use a separate practice database or rename the example tables before running the setup.
### Create the tables
```sql
CREATE TABLE employees_current (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees_payroll (
employee_id INTEGER PRIMARY KEY,
salary NUMERIC(10, 2) NOT NULL
);
```
### Insert the sample data
```sql
INSERT INTO employees_current (employee_id, employee_name)
VALUES
(1, 'Asha'),
(2, 'Rahul'),
(3, 'Meera');
INSERT INTO employees_payroll (employee_id, salary)
VALUES
(1, 50000.00),
(2, 60000.00),
(4, 45000.00);
```
### Verify both datasets
```sql
SELECT * FROM employees_current ORDER BY employee_id;
SELECT * FROM employees_payroll ORDER BY employee_id;
```
The two tables deliberately contain one unmatched record on each side. That makes the behavior of FULL OUTER JOIN easy to observe.
These tables are independent practice datasets. There is no foreign key between them because the example is intended to demonstrate how a full join can compare records that do not all exist on both sides.
4. FULL OUTER JOIN vs Other Joins
The easiest way to understand join types is to ask which rows they preserve.
| Join type | What it preserves |
|---|---|
| INNER JOIN | Matching row combinations only |
| LEFT JOIN | Every left-side row, plus matching right-side rows |
| RIGHT JOIN | Every right-side row, plus matching left-side rows |
| FULL OUTER JOIN | Every row from both sides, matched where possible |
| CROSS JOIN | Every combination of a left-side row and a right-side row |
### A simple way to remember
- INNER: keep matches.
- LEFT: keep everything on the left.
- RIGHT: keep everything on the right.
- FULL: keep everything on both sides.
- CROSS: combine everything with everything.
### Important detail about duplicates
A join does not automatically produce one row per key.
If a key appears multiple times on both sides and the join condition matches those rows, the result can contain multiple combinations.
For example, if one key matches two rows on the left and three rows on the right, that key can contribute six matched rows.
FULL OUTER JOIN preserves unmatched rows, but it does not automatically remove duplicates or enforce one-to-one relationships.
5. Identify Matched and Unmatched Records
A full join is especially useful when you want to compare two datasets and identify which records are missing from either side.
### Find records present only in the current employee dataset
```sql
SELECT
c.employee_id,
c.employee_name
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
WHERE p.employee_id IS NULL
ORDER BY c.employee_id;
```
Expected output:
| employee_id | employee_name |
|---:|---|
| 3 | Meera |
The payroll employee ID is NULL in the result because no matching payroll row exists.
### Find records present only in payroll
```sql
SELECT
p.employee_id,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
WHERE c.employee_id IS NULL
ORDER BY p.employee_id;
```
Expected output:
| employee_id | salary |
|---:|---:|
| 4 | 45000 |
### Find records present in either dataset but not both
```sql
SELECT
c.employee_id AS current_employee_id,
c.employee_name,
p.employee_id AS payroll_employee_id,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
WHERE c.employee_id IS NULL
OR p.employee_id IS NULL;
```
This returns both unmatched cases: Meera from the current dataset and employee 4 from payroll.
The pattern is useful for reconciliation, data migration checks, and comparing exports from two systems.
Here the ID columns are primary keys, so they cannot be NULL in actual source rows. That makes IS NULL a reliable unmatched-row test.
6. Use COALESCE to Display a Common Key
In a full join, an unmatched row has an ID on one side and NULL on the other.
For example:
| current_employee_id | payroll_employee_id |
|---:|---:|
| 3 | NULL |
| NULL | 4 |
If you want a single column containing the ID from either side, use COALESCE.
### Example
```sql
SELECT
COALESCE(c.employee_id, p.employee_id) AS employee_id,
c.employee_name,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
ORDER BY employee_id;
```
COALESCE returns the first non-NULL argument.
- For a matched employee, both IDs are present, so the current employee ID is returned.
- For employee 3, the current employee ID is returned.
- For employee 4, the payroll employee ID is returned.
### Why not just select c.employee_id?
Because employee 4 exists only in payroll. Its current employee ID is NULL, so selecting only c.employee_id would hide the available ID.
COALESCE does not create a match or change the join. It only chooses which value to display.
7. CROSS JOIN: Generate Every Combination
## What is a Cartesian product?
A Cartesian product combines every row from one input with every row from another input.
If the first table has 3 rows and the second has 2 rows, CROSS JOIN returns:
```
3 × 2 = 6 rows
```
### Syntax
```sql
SELECT
a.column_name,
b.column_name
FROM table_a AS a
CROSS JOIN table_b AS b;
```
Unlike an ordinary equality join, CROSS JOIN does not require an ON condition.
### Example: Colors and sizes
Suppose you have these values.
**colors**
| color |
|---|
| Red |
| Blue |
**sizes**
| size |
|---|
| Small |
| Medium |
| Large |
### Query
```sql
SELECT
c.color,
s.size
FROM colors AS c
CROSS JOIN sizes AS s
ORDER BY c.color, s.size;
```
### Expected output
| color | size |
|---|---|
| Blue | Large |
| Blue | Medium |
| Blue | Small |
| Red | Large |
| Red | Medium |
| Red | Small |
There are six combinations because two colors multiplied by three sizes equals six rows.
The ORDER BY sorts the result alphabetically. Without ORDER BY, SQL does not guarantee the output order.
Generate every color and size combination
CROSS JOIN combines each color with every size. The six rows are the Cartesian product of the two input sets.
8. Set Up the CROSS JOIN Example
Create two small tables to run the CROSS JOIN query.
### Create the tables
```sql
CREATE TABLE colors (
color VARCHAR(30) PRIMARY KEY
);
CREATE TABLE sizes (
size VARCHAR(30) PRIMARY KEY
);
```
### Insert values
```sql
INSERT INTO colors (color)
VALUES ('Red'), ('Blue');
INSERT INTO sizes (size)
VALUES ('Small'), ('Medium'), ('Large');
```
### Run the query
```sql
SELECT
c.color,
s.size
FROM colors AS c
CROSS JOIN sizes AS s
ORDER BY c.color, s.size;
```
The query produces six combinations.
To predict a CROSS JOIN's result size, multiply the number of rows in each input. If colors contains 2 rows and sizes contains 3 rows, the result contains 6 rows.
For three inputs with 2, 3, and 4 rows, a chained Cartesian product can produce 2 × 3 × 4 = 24 combinations.
These calculations help you recognize potentially large results before executing a query.
9. Real-World Uses of CROSS JOIN
CROSS JOIN is useful when every combination is required.
### Use case 1: Generate test scenarios
Suppose you need to test an application across browser and operating-system combinations.
```sql
SELECT
b.browser_name,
o.os_name
FROM browsers AS b
CROSS JOIN operating_systems AS o
ORDER BY b.browser_name, o.os_name;
```
If there are four browsers and three operating systems, the query generates twelve combinations.
This can help prepare a test matrix, although it does not guarantee that every combination is supported by your application.
### Use case 2: Create a reporting matrix
You may need every month and every product category to appear in a report, even when some combinations have no sales.
A calendar or month table can be CROSS JOINed with a category table. You can then LEFT JOIN the sales data to that matrix.
Conceptually:
```sql
SELECT
m.month_start,
c.category_id
FROM reporting_months AS m
CROSS JOIN product_categories AS c;
```
This generates the required month-category combinations. A subsequent LEFT JOIN can attach sales information, with missing sales values handled appropriately.
### Use case 3: Generate parameter combinations
CROSS JOIN can combine candidate values for configuration testing, pricing experiments, or simulation inputs.
### Important warning
CROSS JOIN should be intentional. Accidentally omitting a join condition from an ordinary join can produce a Cartesian product and unexpectedly multiply rows.
Always check whether every combination is actually required.
10. CROSS JOIN Result Size and Safety
A CROSS JOIN can produce a large result very quickly.
| Rows in table A | Rows in table B | Result rows |
|---:|---:|---:|
| 2 | 3 | 6 |
| 10 | 20 | 200 |
| 100 | 500 | 50,000 |
| 1,000 | 1,000 | 1,000,000 |
| 10,000 | 10,000 | 100,000,000 |
The number of rows is the product of the two input row counts.
### Before running a large CROSS JOIN
1. Estimate the result size.
2. Filter each input first if fewer combinations are needed.
3. Select only the columns you actually need.
4. Use LIMIT when you only need a small sample for inspection.
5. Inspect the plan for expensive operations when appropriate.
Example:
```sql
SELECT
c.color,
s.size
FROM colors AS c
CROSS JOIN sizes AS s
LIMIT 10;
```
LIMIT restricts the rows returned, but it does not change the logical requirement to form combinations. The actual execution work depends on the plan and what the query needs to do.
A CROSS JOIN is not inherently wrong or inefficient. It is the correct operation when every combination is part of the requirement.
11. Can You Combine These Joins with Aggregates?
Yes. FULL OUTER JOIN and CROSS JOIN can be combined with aggregates, grouping, and other joins.
### Example: Count records from both datasets
```sql
SELECT
CASE
WHEN c.employee_id IS NOT NULL
AND p.employee_id IS NOT NULL
THEN 'Matched'
WHEN c.employee_id IS NOT NULL
THEN 'Current only'
ELSE 'Payroll only'
END AS record_status,
COUNT(*) AS record_count
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
GROUP BY
CASE
WHEN c.employee_id IS NOT NULL
AND p.employee_id IS NOT NULL
THEN 'Matched'
WHEN c.employee_id IS NOT NULL
THEN 'Current only'
ELSE 'Payroll only'
END
ORDER BY record_status;
```
Expected output:
| record_status | record_count |
|---|---:|
| Current only | 1 |
| Matched | 2 |
| Payroll only | 1 |
This works because each table has unique employee IDs in the example.
If either side contained multiple rows for a matching key, the join could create several matched combinations. In that case, COUNT(*) would count combinations, not necessarily distinct employees.
### A useful distinction
Aggregating a join result does not undo row multiplication. Always understand the number of rows produced by the join before interpreting COUNT, SUM, or AVG.
12. Guided Practice
Use the employees_current, employees_payroll, colors, and sizes tables from this lesson.
### Exercise 1: Write a FULL OUTER JOIN
Return all employee IDs and names from employees_current, along with any matching payroll salary. Keep employees present on either side.
### Exercise 2: Find current-only employees
Return the current employee IDs and names for employees who have no payroll row.
### Exercise 3: Find payroll-only employees
Return the payroll employee IDs and salaries for records that do not have a matching current employee.
### Exercise 4: Display one employee ID
Write a full join that displays one combined employee_id column using COALESCE, along with employee name and salary.
### Exercise 5: Generate combinations
Return every color-size combination and sort the output by color and size.
### Exercise 6: Predict row count
Table A contains 7 rows and table B contains 5 rows. How many rows does a CROSS JOIN produce?
### Exercise 7: Diagnose a large result
A developer expects 100 matching records but receives thousands of rows after combining two tables. What should they inspect first?
### Exercise 8: Reconciliation
Explain why FULL OUTER JOIN is useful when comparing two exports that may contain different sets of IDs.
13. Practice Solutions
### Solution 1: Full join
```sql
SELECT
COALESCE(c.employee_id, p.employee_id) AS employee_id,
c.employee_name,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
ORDER BY employee_id;
```
This keeps matching rows and unmatched rows from both datasets.
### Solution 2: Current-only employees
```sql
SELECT
c.employee_id,
c.employee_name
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
WHERE p.employee_id IS NULL
ORDER BY c.employee_id;
```
### Solution 3: Payroll-only records
```sql
SELECT
p.employee_id,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id
WHERE c.employee_id IS NULL
ORDER BY p.employee_id;
```
### Solution 4: Combined employee ID
```sql
SELECT
COALESCE(c.employee_id, p.employee_id) AS employee_id,
c.employee_name,
p.salary
FROM employees_current AS c
FULL OUTER JOIN employees_payroll AS p
ON c.employee_id = p.employee_id;
```
### Solution 5: Color-size combinations
```sql
SELECT
c.color,
s.size
FROM colors AS c
CROSS JOIN sizes AS s
ORDER BY c.color, s.size;
```
### Solution 6: Predict row count
7 × 5 = **35 rows**.
### Solution 7: Diagnose the large result
Check whether the join predicate is missing or incorrect, whether the join key is unique on the expected side, and whether a many-to-many relationship is producing multiple combinations.
### Solution 8: Reconciliation
FULL OUTER JOIN keeps records that match and records that appear in only one export. It allows you to identify missing or unmatched IDs without discarding either dataset's unmatched rows.
14. Knowledge Check: MCQs
Choose the best answer for each question and read the explanation.
15. SQL Interview Questions and Answers
### 1. What is FULL OUTER JOIN?
It returns matched row combinations and all unmatched rows from both inputs. Columns from the nonmatching side are NULL for an unmatched row.
### 2. What is the difference between FULL JOIN and FULL OUTER JOIN?
In PostgreSQL, they are equivalent. OUTER is optional.
### 3. When would you use FULL OUTER JOIN?
Use it when comparing or reconciling datasets and you need to retain records present on either side, including records that do not match.
### 4. What is CROSS JOIN?
CROSS JOIN produces every combination of rows from the two inputs.
### 5. How do you calculate the result size of a CROSS JOIN?
Multiply the number of rows in the first input by the number of rows in the second input.
### 6. Does CROSS JOIN require a join condition?
No. It intentionally produces combinations without an ON condition.
### 7. How can you find records that exist only in the left dataset?
Use FULL OUTER JOIN and filter with IS NULL on a suitable non-nullable key from the right side, or use an anti-join pattern such as NOT EXISTS.
### 8. How can you find records that exist only in the right dataset?
Use FULL OUTER JOIN and test whether a suitable non-nullable key from the left side IS NULL.
### 9. Why use COALESCE after a FULL OUTER JOIN?
It returns the first non-NULL argument, allowing a common identifier to be displayed whether the record came from the left, right, or both sides.
### 10. Does FULL OUTER JOIN automatically remove duplicates?
No. It preserves row combinations according to the join condition. Duplicate keys and one-to-many or many-to-many relationships can produce multiple rows.
### 11. Can CROSS JOIN be useful in testing?
Yes. It can generate test combinations, such as browsers paired with operating systems, when all combinations are needed.
### 12. Can CROSS JOIN generate a reporting matrix?
Yes. For example, it can combine months and product categories before a LEFT JOIN attaches available sales data.
### 13. Why can CROSS JOIN cause performance problems?
Its result size grows as the product of the input row counts. Large inputs can create a huge number of combinations.
### 14. Is CROSS JOIN always a mistake in production?
No. It is appropriate when every combination is required. The key is to ensure that the Cartesian product is intentional.
### 15. What is a reconciliation query?
It compares datasets to identify matching and unmatched records. FULL OUTER JOIN is one useful approach when both sides must be preserved.
### 16. Can a FULL OUTER JOIN produce several rows for one key?
Yes. If multiple rows match the same key on both sides, each matching combination may appear.
### 17. What is the difference between FULL OUTER JOIN and UNION?
A join combines columns from related or paired rows. UNION combines rows from compatible query outputs. They solve different problems.
### 18. How do you troubleshoot unexpectedly large join results?
Check the join condition, key uniqueness, relationship cardinality, and whether multiple matching rows create combinations. Inspect the execution plan when performance is a concern.
16. Summary and Final Checklist
## Summary
- FULL OUTER JOIN preserves matched and unmatched rows from both inputs.
- FULL JOIN is shorthand for FULL OUTER JOIN in PostgreSQL.
- Unmatched rows contain NULL values for columns from the missing side.
- COALESCE can display a common ID from either side.
- IS NULL on a suitable non-nullable key can identify records missing from one dataset.
- CROSS JOIN generates every possible combination of rows from two inputs.
- A CROSS JOIN of m and n rows returns m × n rows.
- CROSS JOIN is useful for test matrices, reporting grids, and parameter combinations.
- An accidental Cartesian product can create a very large result.
- Join keys are not automatically unique unless the schema enforces uniqueness.
- A join does not automatically deduplicate the result.
- Use ORDER BY when output order matters.
## Before moving on
- [ ] Explain which rows FULL OUTER JOIN preserves.
- [ ] Write a full join that compares two datasets.
- [ ] Find rows present only on the left side.
- [ ] Find rows present only on the right side.
- [ ] Use COALESCE to display a combined key.
- [ ] Calculate the expected size of a Cartesian product.
- [ ] Generate a color-size combination matrix using CROSS JOIN.
- [ ] Explain why duplicate join keys can multiply result rows.
- [ ] Recognize when a Cartesian product is intentional versus accidental.
**Next step:** Learn self joins to compare rows within the same table.
17. Common Mistakes
Avoid these mistakes when using full joins and Cartesian products.
Common pitfalls to avoid
Real debugging lessons and exact syntax contrasts to avoid runtime bugs
#1 Using INNER JOIN when unmatched rows must remain
Why it happens: INNER JOIN removes rows that have no match on the other side.
Key takeaway: Use FULL OUTER JOIN when both datasets' unmatched records must be preserved.
#2 Selecting only the left-side ID after a full join
Why it happens: Rows present only in the right dataset have NULL in the left-side ID column.
Key takeaway: Use COALESCE when you need a single identifier from either side.
#3 Running an unintended Cartesian product
Why it happens: CROSS JOIN returns every combination of input rows.
Key takeaway: Use CROSS JOIN intentionally, estimate the result size, and filter the inputs when the requirement allows it.
Ready for hands-on challenges?
Test your skills with shuffled coding challenges in the Practice Arena.
Key takeaways
- FULL OUTER JOIN preserves matching rows and unmatched rows from both inputs.
- FULL OUTER JOIN fills columns from the missing side with NULL for unmatched rows.
- CROSS JOIN returns every combination of rows from its two inputs.
- A CROSS JOIN between tables with m and n rows returns m multiplied by n rows.
- Use COALESCE to display the first non-NULL value from either side of a full join.
- A full join's matching condition belongs in ON; a NULL check can help identify unmatched rows.
- CROSS JOIN is useful for combinations, calendars, test data, and parameter grids.
- A CROSS JOIN can produce a very large result, so estimate its size before running it.
- Neither join type should be chosen merely because it sounds more advanced; choose it based on the required result.