Why joins are essential in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INNER JOIN query?

Consider two tables: students and enrollments.

students has columns: student_id, name.

enrollments has columns: student_id, course.

What rows will this query return?

SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
ORDER BY students.name;

PostgreSQL

CREATE TABLE students (student_id INT, name TEXT);
INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
CREATE TABLE enrollments (student_id INT, course TEXT);
INSERT INTO enrollments VALUES (1, 'Math'), (1, 'Science'), (3, 'History');

A [{"name": "Alice", "course": "Math"}, {"name": "Alice", "course": "Science"}, {"name": "Bob", "course": null}, {"name": "Charlie", "course": "History"}]

B [{"name": "Alice", "course": "Math"}, {"name": "Bob", "course": null}, {"name": "Charlie", "course": "History"}]

C [{"name": "Alice", "course": "Math"}, {"name": "Alice", "course": "Science"}, {"name": "Charlie", "course": "History"}]

D [{"name": "Alice", "course": null}, {"name": "Bob", "course": null}, {"name": "Charlie", "course": null}]

💡 Hint

INNER JOIN returns only matching rows from both tables.

The INNER JOIN returns rows where student_id matches in both tables. Bob has no enrollment, so he is excluded.

🧠 Conceptual

intermediate

Why do we use JOINs in databases?

Which of the following best explains why JOINs are essential in relational databases?

A JOINs combine rows from two or more tables based on related columns to retrieve meaningful combined data.

B JOINs create new tables permanently by merging existing tables.

C JOINs delete duplicate rows from a single table to clean data.

D JOINs are used to encrypt data between tables for security.

💡 Hint

Think about how data is stored in separate tables but often needs to be seen together.

JOINs let us combine related data from multiple tables to answer questions that involve more than one table.

📝 Syntax

advanced

Which query correctly uses a LEFT JOIN to include all students?

Given the same students and enrollments tables, which query will return all students including those without enrollments?

A SELECT students.name, enrollments.course FROM students LEFT JOIN enrollments ON students.student_id = enrollments.student_id;

B SELECT students.name, enrollments.course FROM students INNER JOIN enrollments ON students.student_id = enrollments.student_id;

C SELECT students.name, enrollments.course FROM enrollments LEFT JOIN students ON students.student_id = enrollments.student_id;

D SELECT students.name, enrollments.course FROM students RIGHT JOIN enrollments ON students.student_id = enrollments.student_id;

💡 Hint

LEFT JOIN keeps all rows from the left table.

LEFT JOIN returns all rows from students even if no matching enrollment exists. INNER JOIN excludes unmatched rows.

❓ optimization

advanced

How can JOINs improve query performance compared to multiple separate queries?

Why is using JOINs often more efficient than running separate queries and combining results in application code?

A JOINs force the database to scan all tables fully, making queries slower than separate queries.

B JOINs let the database engine optimize data retrieval and reduce the number of queries sent, lowering network overhead.

C JOINs require creating temporary tables which increases disk usage and slows performance.

D JOINs always use more memory and slow down queries compared to separate queries.

💡 Hint

Think about how databases handle data internally versus application code.

JOINs allow the database to combine data efficiently in one query, reducing network trips and leveraging indexes.

🔧 Debug

expert

What error does this JOIN query produce?

Given the tables students(student_id, name) and enrollments(student_id, course), what error will this query raise?

SELECT students.name, enrollments.course
FROM students
JOIN enrollments ON students.id = enrollments.student_id;

A ERROR: syntax error at or near "JOIN"

B ERROR: column "enrollments.name" does not exist

C No error, query runs successfully

D ERROR: column "students.id" does not exist

💡 Hint

Check the column names used in the ON clause carefully.

The students table has no column named id, so referencing students.id causes an error.

Practice

1. Why are JOINs essential in PostgreSQL when working with multiple tables?

easy

A. They delete duplicate rows from a table.

B. They speed up the database server hardware.

C. They create new tables automatically.

D. They combine related data from different tables into one result.

Solution

1. Step 1: Understand the purpose of JOINs

   JOINs are used to combine rows from two or more tables based on a related column between them.

2. Step 2: Identify what JOINs do not do

   JOINs do not delete rows, create tables, or affect hardware speed.

3. Final Answer:

   They combine related data from different tables into one result. -> Option D

4. Quick Check:

   JOINs combine tables = B [OK]

Hint: Remember: JOINs merge related rows from tables [OK]

Common Mistakes:

• Thinking JOINs delete or create tables
• Confusing JOINs with database hardware functions
• Assuming JOINs work without related columns

2. Which of the following is the correct syntax to join two tables employees and departments on the column department_id?

easy

A. SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

B. SELECT * FROM employees WHERE department_id = departments.department_id;

C. SELECT * FROM employees JOIN departments USING (department_id);

D. SELECT * FROM employees JOIN departments ON employees.id = departments.id;

Solution

1. Step 1: Check the JOIN syntax with ON clause

   The correct JOIN syntax uses ON to specify the matching columns: employees.department_id = departments.department_id.

2. Step 2: Verify other options

   SELECT * FROM employees WHERE department_id = departments.department_id; uses WHERE incorrectly for joining. SELECT * FROM employees JOIN departments USING (department_id); uses correct USING syntax with parentheses. SELECT * FROM employees JOIN departments ON employees.id = departments.id; joins on wrong columns (id instead of department_id).

3. Final Answer:

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; -> Option A

4. Quick Check:

   JOIN with ON correct columns = C [OK]

Hint: Use ON with matching columns for JOINs [OK]

Common Mistakes:

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

3. Given two tables:
students(id, name)
grades(student_id, score)
What will be the result of this query?

SELECT students.name, grades.score FROM students JOIN grades ON students.id = grades.student_id;

medium

A. A list of scores without student names.

B. A list of student names with their corresponding scores.

C. An error because JOIN syntax is wrong.

D. A list of all students with NULL scores.

Solution

1. Step 1: Understand the JOIN condition

   The JOIN matches students.id with grades.student_id, linking each student to their scores.

2. Step 2: Predict the output columns

   The SELECT returns student names and their scores from matching rows only.

3. Final Answer:

   A list of student names with their corresponding scores. -> Option B

4. Quick Check:

   JOIN matches students to scores = D [OK]

Hint: JOIN returns matched rows combining columns [OK]

Common Mistakes:

• Assuming all students appear even without scores
• Thinking JOIN causes syntax error here
• Expecting only scores without names

4. Identify the error in this JOIN query:

SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.idd;

medium

A. Column name 'idd' does not exist in departments table.

B. Missing JOIN keyword.

C. Using table aliases incorrectly.

D. SELECT statement missing FROM clause.

Solution

1. Step 1: Check the JOIN condition columns

   The query uses 'd.idd' which is likely a typo; the correct column is probably 'd.id'.

2. Step 2: Confirm other syntax parts

   JOIN keyword and aliases are correctly used; FROM clause is present.

3. Final Answer:

   Column name 'idd' does not exist in departments table. -> Option A

4. Quick Check:

   Typo in column name = A [OK]

Hint: Check column names carefully in JOIN conditions [OK]

Common Mistakes:

• Typos in column names in ON clause
• Confusing table aliases usage
• Missing FROM clause (not in this case)

5. You have two tables:
orders(order_id, customer_id, amount)
customers(customer_id, name)
You want to list all customers and their total order amounts, including customers with no orders. Which query achieves this?

hard

A. SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

C. SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand the requirement for all customers

   To include customers with no orders, use LEFT JOIN from customers to orders.

2. Step 2: Use aggregation with GROUP BY

   SUM(o.amount) calculates total orders per customer; GROUP BY c.name groups results by customer.

3. Step 3: Check other options

   SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id; excludes customers without orders (INNER JOIN). SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name; reverses join direction, losing customers without orders. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; uses RIGHT JOIN but groups by customer name from customers table, which may cause errors or exclude customers.

4. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; -> Option C

5. Quick Check:

   LEFT JOIN keeps all customers = A [OK]

Hint: Use LEFT JOIN to keep all from left table [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Confusing LEFT and RIGHT JOIN directions
• Forgetting GROUP BY when using aggregation