Cartesian product and joins in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Cartesian product in the context of databases?

A Cartesian product is the result of combining every row of one table with every row of another table. It pairs all possible combinations of rows from both tables.

beginner

What is the main difference between a Cartesian product and a join?

A Cartesian product combines all rows from two tables without any condition, while a join combines rows based on a related column or condition, filtering the results.

beginner

Name the common types of joins in databases.

The common types of joins are: INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

beginner

What does an INNER JOIN do?

An INNER JOIN returns only the rows where there is a match in both tables based on the join condition.

beginner

Why is a Cartesian product usually not desired in queries?

Because it creates all possible combinations of rows, which can lead to very large and meaningless result sets if not filtered properly.

What does a Cartesian product between two tables produce?

A All possible combinations of rows from both tables

B Only matching rows based on a condition

C Rows from the first table only

D Rows from the second table only

A Cartesian product pairs every row of the first table with every row of the second table, creating all possible combinations.

Which join returns only rows with matching values in both tables?

A INNER JOIN

B LEFT JOIN

C RIGHT JOIN

D FULL JOIN

INNER JOIN returns rows where the join condition matches in both tables.

What happens if you run a query joining two tables without a join condition?

A It returns an error

B It returns a Cartesian product

C It returns only the first table's rows

D It returns only the second table's rows

Without a join condition, the query produces a Cartesian product, combining all rows from both tables.

Which join includes all rows from the left table and matching rows from the right table?

A RIGHT JOIN

B INNER JOIN

C LEFT JOIN

D FULL JOIN

LEFT JOIN returns all rows from the left table and matched rows from the right table; unmatched right rows are NULL.

What is a risk of using Cartesian products in large tables?

A It speeds up queries

B It reduces data size

C It filters data automatically

D It creates very large result sets

Cartesian products multiply the number of rows, which can create huge and often useless result sets.

Explain in your own words what a Cartesian product is and why it might be problematic in database queries.

Think about what happens when you pair every row from one table with every row from another.

Describe the difference between INNER JOIN and LEFT JOIN with examples of when you might use each.

Consider what happens to rows that don't have a match in the other table.

Practice

1. What does a Cartesian product between two tables produce?

easy

A. Only rows with matching values in both tables

B. All possible pairs of rows from both tables

C. Only rows from the first table

D. Only rows from the second table

Solution

1. Step 1: Understand Cartesian product definition

   A Cartesian product combines every row of the first table with every row of the second table.

2. Step 2: Identify the result of combining rows

   This means the total rows in the result equal the number of rows in the first table multiplied by the number of rows in the second table.

3. Final Answer:

   All possible pairs of rows from both tables -> Option B

4. Quick Check:

   Cartesian product = all pairs [OK]

Hint: Cartesian product pairs every row with every other row [OK]

Common Mistakes:

• Confusing Cartesian product with INNER JOIN
• Thinking it returns only matching rows
• Assuming it returns rows from only one table

2. Which SQL keyword is used to combine rows from two tables based on a matching condition?

easy

A. GROUP BY

B. CROSS JOIN

C. UNION

D. INNER JOIN

Solution

1. Step 1: Identify join types

   INNER JOIN combines rows from two tables where a specified condition matches.

2. Step 2: Match keyword to function

   CROSS JOIN produces Cartesian product, UNION combines result sets vertically, GROUP BY groups rows. Only INNER JOIN matches rows based on condition.

3. Final Answer:

   INNER JOIN -> Option D

4. Quick Check:

   Matching rows use INNER JOIN [OK]

Hint: INNER JOIN returns only matching rows [OK]

Common Mistakes:

• Choosing CROSS JOIN for matching rows
• Confusing UNION with JOIN
• Using GROUP BY to combine tables

3. Consider two tables:
Table A: IDs 1, 2
Table B: IDs 3, 4
What is the number of rows returned by SELECT * FROM A CROSS JOIN B;?

medium

A. 4

B. 0

C. 1

D. 2

Solution

1. Step 1: Count rows in each table

   Table A has 2 rows, Table B has 2 rows.

2. Step 2: Calculate Cartesian product size

   CROSS JOIN returns all pairs: 2 rows * 2 rows = 4 rows.

3. Final Answer:

   4 -> Option A

4. Quick Check:

   2 * 2 = 4 rows [OK]

Hint: Multiply row counts for CROSS JOIN result size [OK]

Common Mistakes:

• Counting only rows from one table
• Confusing CROSS JOIN with INNER JOIN
• Assuming zero rows if no matching IDs

4. Identify the error in this SQL query:
SELECT * FROM Employees INNER JOIN Departments;

medium

A. SELECT * is invalid syntax

B. INNER JOIN cannot be used with two tables

C. Missing ON clause for join condition

D. Departments table does not exist

Solution

1. Step 1: Review INNER JOIN syntax

   INNER JOIN requires an ON clause to specify how tables relate.

2. Step 2: Check query for ON clause

   The query lacks ON condition, so SQL cannot match rows properly.

3. Final Answer:

   Missing ON clause for join condition -> Option C

4. Quick Check:

   INNER JOIN needs ON clause [OK]

Hint: Always include ON clause with INNER JOIN [OK]

Common Mistakes:

• Omitting ON clause in JOIN
• Thinking INNER JOIN works without condition
• Assuming SELECT * is incorrect

5. You have two tables:
Students (id, name)
Grades (student_id, score)
Which join will return all students and their scores, showing NULL if a student has no grade?

hard

A. LEFT JOIN

B. INNER JOIN

C. RIGHT JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand join types and requirements

   LEFT JOIN returns all rows from the left table (Students) and matching rows from the right (Grades). If no match, NULL appears.

2. Step 2: Match requirement to join type

   We want all students even if they have no grades, so LEFT JOIN is correct.

3. Final Answer:

   LEFT JOIN -> Option A

4. Quick Check:

   All left table rows with NULL for no match = LEFT JOIN [OK]

Hint: LEFT JOIN keeps all left table rows, fills NULL if no match [OK]

Common Mistakes:

• Using INNER JOIN which excludes unmatched rows
• Choosing RIGHT JOIN which keeps right table rows
• Confusing CROSS JOIN with conditional joins