Cartesian product and joins in DBMS Theory - Full Explanation

Introduction

Imagine you have two lists of items and want to see every possible pair made by picking one item from each list. This is the problem that Cartesian product solves. Joins build on this idea to combine related data from two tables in a meaningful way.

Explanation

Cartesian Product

The Cartesian product takes every row from the first table and pairs it with every row from the second table. This creates a new table with all possible combinations of rows. It is the foundation for how joins work but usually produces many more rows than needed.

Cartesian product pairs every row of one table with every row of another, creating all possible combinations.

Inner Join

An inner join combines rows from two tables only when they have matching values in a specified column. It filters the Cartesian product to show only related pairs, making the result smaller and more useful.

Inner join returns rows where there is a match in both tables based on a condition.

Left Join

A left join returns all rows from the first (left) table and the matching rows from the second (right) table. If there is no match, the result still includes the left table row with null values for the right table columns.

Left join keeps all rows from the left table and adds matching rows from the right table or nulls if no match.

Right Join

A right join is the opposite of a left join. It returns all rows from the right table and the matching rows from the left table. If no match exists, the left table columns show null values.

Right join keeps all rows from the right table and adds matching rows from the left table or nulls if no match.

Full Outer Join

A full outer join returns all rows from both tables. When there is a match, it combines the rows. When there is no match, it fills in null values for the missing side. This join shows everything from both tables.

Full outer join includes all rows from both tables, matching where possible and filling nulls where not.

Real World Analogy

Imagine two groups of people at a party. The Cartesian product is like pairing every person from the first group with every person from the second group, no matter if they know each other. Joins are like pairing only those who share a common interest or connection.

Cartesian Product → Pairing every person from group A with every person from group B, regardless of connection

Inner Join → Pairing only people from both groups who share a common hobby

Left Join → Listing all people from group A and pairing them with friends from group B if they have any, otherwise showing them alone

Right Join → Listing all people from group B and pairing them with friends from group A if they have any, otherwise showing them alone

Full Outer Join → Listing everyone from both groups, pairing friends where possible, and showing others alone

Diagram

Diagram

┌─────────────┐     ┌─────────────┐
│  Table A    │     │  Table B    │
│  (Rows: 2) │     │  (Rows: 3) │
└─────┬───────┘     └─────┬───────┘
      │                   │
      │                   │
      │ Cartesian Product  │
      │ (2 x 3 = 6 rows)  │
      ▼                   ▼
┌───────────────────────────────┐
│ Result: All pairs of rows from │
│ Table A and Table B (6 rows)   │
└───────────────────────────────┘

Inner Join filters this result to only matching pairs based on a condition.

This diagram shows how the Cartesian product combines every row from Table A with every row from Table B, creating all possible pairs.

Key Facts

Cartesian Product → Combines every row of one table with every row of another, producing all possible pairs.

Inner Join → Returns only rows with matching values in both tables based on a join condition.

Left Join → Returns all rows from the left table and matching rows from the right table or nulls if no match.

Right Join → Returns all rows from the right table and matching rows from the left table or nulls if no match.

Full Outer Join → Returns all rows from both tables, matching where possible and filling nulls where not.

Code Example

DBMS Theory

import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

# Create two tables
cur.execute('CREATE TABLE A (id INTEGER, name TEXT)')
cur.execute('CREATE TABLE B (id INTEGER, color TEXT)')

# Insert data
cur.execute("INSERT INTO A VALUES (1, 'Alice')")
cur.execute("INSERT INTO A VALUES (2, 'Bob')")
cur.execute("INSERT INTO B VALUES (1, 'Red')")
cur.execute("INSERT INTO B VALUES (2, 'Blue')")
cur.execute("INSERT INTO B VALUES (3, 'Green')")

# Cartesian product
cur.execute('SELECT A.name, B.color FROM A, B')
cartesian = cur.fetchall()

# Inner join
cur.execute('SELECT A.name, B.color FROM A INNER JOIN B ON A.id = B.id')
inner_join = cur.fetchall()

# Left join
cur.execute('SELECT A.name, B.color FROM A LEFT JOIN B ON A.id = B.id')
left_join = cur.fetchall()

print('Cartesian Product:', cartesian)
print('Inner Join:', inner_join)
print('Left Join:', left_join)

Common Confusions

Believing Cartesian product only shows related rows.

Believing Cartesian product only shows related rows. Cartesian product shows all possible pairs without filtering; joins apply conditions to find related rows.

Thinking left join and right join return the same results.

Thinking left join and right join return the same results. Left join keeps all rows from the left table; right join keeps all rows from the right table, so results differ based on which table is on which side.

Assuming full outer join is just a combination of inner joins.

Assuming full outer join is just a combination of inner joins. Full outer join includes all rows from both tables, not just matching ones, filling missing parts with nulls.

Summary

Cartesian product creates all possible pairs of rows from two tables without filtering.

Joins use conditions to combine rows meaningfully, with inner join showing only matches.

Left, right, and full outer joins control which rows to keep when matches are missing.