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.