Why joins are needed in SQL - Visual Breakdown

Concept Flow - Why joins are needed

Start with Table A

↓

Identify related data

↓

Combine matching rows

↓

Create joined result

↓

Use combined data

We start with two tables, find related rows, combine them, and get a new table with connected information.

Execution Sample

SQL

SELECT employees.name, departments.name
FROM employees
JOIN departments ON employees.dept_id = departments.id;

This query joins employees with their departments to show employee names alongside their department names.

Execution Table

Step	Action	employees row	departments row	Match Condition	Output row
1	Check employees row 1 with departments row 1	{id:1, name:'Alice', dept_id:10}	{id:10, name:'HR'}	10 = 10 (True)	{employee_name:'Alice', department_name:'HR'}
2	Check employees row 1 with departments row 2	{id:1, name:'Alice', dept_id:10}	{id:20, name:'Sales'}	10 = 20 (False)	No output
3	Check employees row 2 with departments row 1	{id:2, name:'Bob', dept_id:20}	{id:10, name:'HR'}	20 = 10 (False)	No output
4	Check employees row 2 with departments row 2	{id:2, name:'Bob', dept_id:20}	{id:20, name:'Sales'}	20 = 20 (True)	{employee_name:'Bob', department_name:'Sales'}
5	Check employees row 3 with departments row 1	{id:3, name:'Carol', dept_id:30}	{id:10, name:'HR'}	30 = 10 (False)	No output
6	Check employees row 3 with departments row 2	{id:3, name:'Carol', dept_id:30}	{id:20, name:'Sales'}	30 = 20 (False)	No output
7	No more rows to check	N/A	N/A	N/A	End

💡 All employees rows checked against all departments rows; join complete.

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
Current employees row	None	{id:1, name:'Alice', dept_id:10}	{id:2, name:'Bob', dept_id:20}	None
Current departments row	None	{id:10, name:'HR'}	{id:20, name:'Sales'}	None
Output rows	[]	[{employee_name:'Alice', department_name:'HR'}]	[{employee_name:'Alice', department_name:'HR'}, {employee_name:'Bob', department_name:'Sales'}]	[{employee_name:'Alice', department_name:'HR'}, {employee_name:'Bob', department_name:'Sales'}]

Key Moments - 2 Insights

Why do we need to check each row of one table against rows of another?

Because related data is stored separately, we must compare rows to find matches, as shown in execution_table steps 1-6.

What happens if no matching department is found for an employee?

That employee's row does not appear in the output, as seen in steps 5 and 6 where no matches produce no output.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output row at step 4?

A {employee_name:'Bob', department_name:'Sales'}

B {employee_name:'Alice', department_name:'HR'}

C No output

D {employee_name:'Carol', department_name:'Sales'}

💡 Hint

Check the 'Output row' column in row 4 of execution_table.

At which step does the join process end?

A Step 6

B Step 5

C Step 7

D Step 4

💡 Hint

Look for the step with 'No more rows to check' in execution_table.

If employees had a dept_id that doesn't exist in departments, what happens to that employee in the output?

A They appear with empty strings

B They do not appear in the output

C They appear with NULL department name

D They appear multiple times

💡 Hint

Refer to steps 5 and 6 where no matching department means no output row.

Concept Snapshot

Joins combine rows from two tables based on a related column.
They match rows where the join condition is true.
Only matching rows appear in the output.
This lets us see connected data from separate tables.
Syntax example: SELECT * FROM A JOIN B ON A.key = B.key;

Full Transcript

Joins are needed because data is often split into different tables to keep it organized. To see related information together, we combine rows from these tables where their related columns match. For example, employees and departments are separate tables. By joining on department IDs, we get employee names with their department names. The process checks each employee row against each department row, outputs combined rows when IDs match, and skips rows without matches. This way, we get a new table showing connected data from both tables.

Practice

1. Why do we use JOIN in SQL when working with multiple tables?

easy

A. To combine related data from two or more tables into one result

B. To delete rows from a table

C. To create a new table

D. To change the data type of a column

Solution

1. Step 1: Understand the purpose of JOIN

   JOIN is used to bring together rows from different tables based on a related column.

2. Step 2: Identify the correct use case

   Deleting rows, creating tables, or changing data types are not done with JOIN.

3. Final Answer:

   To combine related data from two or more tables into one result -> Option A

4. Quick Check:

   JOIN combines tables = To combine related data from two or more tables into one result [OK]

Hint: JOIN merges tables on related columns to see connected data [OK]

Common Mistakes:

• Thinking JOIN deletes or modifies tables
• Confusing JOIN with CREATE or DELETE commands
• Assuming JOIN changes data types

2. Which of the following is the correct syntax to join two tables Employees and Departments on the column DepartmentID?

easy

A. SELECT * FROM Employees WHERE DepartmentID = Departments.DepartmentID;

B. SELECT * FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

C. SELECT * FROM Employees JOIN Departments USING (DepartmentID);

D. SELECT * FROM Employees JOIN Departments ON Employees.ID = Departments.ID;

Solution

1. Step 1: Check the JOIN condition syntax

   The correct JOIN syntax uses ON with matching columns: Employees.DepartmentID = Departments.DepartmentID.

2. Step 2: Verify the options

   SELECT * FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; uses correct ON syntax with matching columns. SELECT * FROM Employees WHERE DepartmentID = Departments.DepartmentID; uses WHERE incorrectly. SELECT * FROM Employees JOIN Departments USING (DepartmentID); uses USING with correct parentheses. SELECT * FROM Employees JOIN Departments ON Employees.ID = Departments.ID; joins on wrong columns.

3. Final Answer:

   SELECT * FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; -> Option B

4. Quick Check:

   JOIN with ON and matching columns = SELECT * FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; [OK]

Hint: Use JOIN ... ON table1.col = table2.col for correct syntax [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Joining on wrong columns
• Misusing USING without parentheses

3. Given two tables:
Students(id, name)
Grades(student_id, grade)
What will this query return?

SELECT Students.name, Grades.grade FROM Students JOIN Grades ON Students.id = Grades.student_id;

medium

A. An error because of missing WHERE clause

B. All students with NULL grades included

C. Only grades without student names

D. A list of student names with their grades where student IDs match

Solution

1. Step 1: Understand INNER JOIN behavior

   JOIN without specifying LEFT or RIGHT is INNER JOIN, which returns rows with matching keys in both tables.

2. Step 2: Analyze the query result

   The query returns student names and grades only where Students.id matches Grades.student_id.

3. Final Answer:

   A list of student names with their grades where student IDs match -> Option D

4. Quick Check:

   INNER JOIN returns matching rows = A list of student names with their grades where student IDs match [OK]

Hint: INNER JOIN returns only matching rows from both tables [OK]

Common Mistakes:

• Expecting all students even without grades
• Thinking JOIN returns unmatched rows
• Assuming WHERE is needed for JOIN condition

4. You wrote this query:

SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.ID;

But it returns an error. What is the most likely cause?

medium

A. The column names in ON clause do not match actual table columns

B. JOIN keyword is not supported in SQL

C. Missing WHERE clause after JOIN

D. SELECT * cannot be used with JOIN

Solution

1. Step 1: Check column names in ON clause

   If column names Orders.CustomerID or Customers.ID do not exist, SQL throws an error.

2. Step 2: Verify other options

   JOIN is valid SQL keyword, WHERE is optional, and SELECT * works with JOIN.

3. Final Answer:

   The column names in ON clause do not match actual table columns -> Option A

4. Quick Check:

   Wrong column names cause JOIN errors = The column names in ON clause do not match actual table columns [OK]

Hint: Check column names in ON clause carefully to avoid errors [OK]

Common Mistakes:

• Assuming JOIN keyword is invalid
• Thinking WHERE is mandatory after JOIN
• Believing SELECT * cannot be used with JOIN

5. You have two tables:
Authors(author_id, name)
Books(book_id, title, author_id)
You want to list all authors and their books, including authors who have no books yet. Which SQL join should you use?

hard

A. INNER JOIN

B. RIGHT JOIN

C. LEFT JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand the requirement

   We want all authors listed, even if they have no books. This means we keep all rows from Authors.

2. Step 2: Choose the correct JOIN type

   LEFT JOIN keeps all rows from the left table (Authors) and matches books if available, else NULL.

3. Step 3: Exclude other JOIN types

   INNER JOIN excludes authors without books, RIGHT JOIN keeps all books, CROSS JOIN creates all combinations.

4. Final Answer:

   LEFT JOIN -> Option C

5. Quick Check:

   LEFT JOIN keeps all left table rows = LEFT JOIN [OK]

Hint: Use LEFT JOIN to keep all rows from the first table [OK]

Common Mistakes:

• Using INNER JOIN and missing authors without books
• Confusing RIGHT JOIN with LEFT JOIN
• Using CROSS JOIN which multiplies rows