LEFT JOIN and RIGHT JOIN in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEFT JOIN with unmatched rows

Given two tables Employees and Departments:

Employees(id, name, dept_id)
Departments(id, dept_name)

What is the output of this query?

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.id
ORDER BY e.id;

PostgreSQL

CREATE TABLE Employees (id INT, name TEXT, dept_id INT);
CREATE TABLE Departments (id INT, dept_name TEXT);
INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', NULL);
INSERT INTO Departments VALUES (10, 'HR'), (30, 'Finance');

A

name | dept_name
-----|----------
Alice | HR
Charlie | NULL

B

name | dept_name
-----|----------
Alice | HR
Bob | Finance
Charlie | NULL

C

name | dept_name
-----|----------
Alice | HR
Bob | NULL
Charlie | NULL

D

name | dept_name
-----|----------
Bob | NULL
Charlie | NULL

💡 Hint

LEFT JOIN keeps all rows from the left table even if there is no matching row in the right table.

The LEFT JOIN returns all employees. For Bob, there is no matching department with id 20, so dept_name is NULL. Charlie has NULL dept_id, so no match, dept_name is NULL.

❓ query_result

intermediate

Output of RIGHT JOIN with unmatched rows

Using the same tables Employees and Departments, what is the output of this query?

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.id
ORDER BY d.id;

PostgreSQL

CREATE TABLE Employees (id INT, name TEXT, dept_id INT);
CREATE TABLE Departments (id INT, dept_name TEXT);
INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', NULL);
INSERT INTO Departments VALUES (10, 'HR'), (30, 'Finance');

A

name | dept_name
-----|----------
Alice | HR
NULL | Finance

B

name | dept_name
-----|----------
Alice | HR
Bob | Finance

C

name | dept_name
-----|----------
Alice | HR
Bob | NULL
NULL | Finance

D

name | dept_name
-----|----------
NULL | HR
NULL | Finance

💡 Hint

RIGHT JOIN keeps all rows from the right table even if there is no matching row in the left table.

The RIGHT JOIN returns all departments. Department 30 (Finance) has no matching employee, so employee name is NULL.

🧠 Conceptual

advanced

Difference between LEFT JOIN and RIGHT JOIN

Which statement correctly describes the difference between LEFT JOIN and RIGHT JOIN?

A LEFT JOIN returns only rows with matches in both tables; RIGHT JOIN returns all rows from the left table only.

B LEFT JOIN and RIGHT JOIN always return the same results but in different order.

C LEFT JOIN returns all rows from the right table; RIGHT JOIN returns all rows from the left table.

D LEFT JOIN returns all rows from the left table and matching rows from the right; RIGHT JOIN returns all rows from the right table and matching rows from the left.

💡 Hint

Think about which table's rows are always kept in the result.

LEFT JOIN keeps all rows from the left table, RIGHT JOIN keeps all rows from the right table, matching rows from the other table if any.

📝 Syntax

advanced

Identify the syntax error in JOIN query

Which option contains a syntax error in the JOIN clause?

A SELECT * FROM A LEFT JOIN B ON A.id = B.id;

B SELECT * FROM A LEFT JOIN B WHERE A.id = B.id;

C SELECT * FROM A RIGHT JOIN B ON A.id = B.id ORDER BY A.id;

D SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

💡 Hint

Check the placement of the ON and WHERE keywords in JOIN syntax.

The ON clause must immediately follow the JOIN keyword to specify join condition. Using WHERE instead causes syntax error.

❓ optimization

expert

Optimizing query with LEFT JOIN and filtering

You want to find all employees and their departments, but only those employees who belong to departments with name starting with 'S'. Which query is most efficient?

A SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.id AND d.dept_name LIKE 'S%';

B SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.id WHERE d.dept_name LIKE 'S%';

C SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.id WHERE d.dept_name LIKE 'S%';

D SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id = d.id WHERE d.dept_name LIKE 'S%';

💡 Hint

Consider where to put the filtering condition to avoid filtering after the join.

Placing the filter in the ON clause limits rows joined, improving efficiency. Filtering in WHERE after LEFT JOIN converts it effectively to INNER JOIN.

Practice

1. What does a LEFT JOIN do in PostgreSQL?

easy

A. Returns all rows from the left table and matching rows from the right table, or NULL if no match.

B. Returns all rows from the right table and matching rows from the left table, or NULL if no match.

C. Returns only rows that have matching values in both tables.

D. Returns all rows from both tables, matching where possible.

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens with unmatched rows

   If there is no matching row in the right table, the result includes NULLs for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, or NULL if no match. -> Option A

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

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

Common Mistakes:

• Confusing LEFT JOIN with RIGHT JOIN
• Thinking LEFT JOIN returns only matched rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a RIGHT JOIN in PostgreSQL?

easy

A. SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 JOIN RIGHT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 RIGHT OUTER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 RIGHT JOIN ON table1.id = table2.id table2;

Solution

1. Step 1: Recall correct RIGHT JOIN syntax

   The correct syntax is: SELECT ... FROM left_table RIGHT JOIN right_table ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   RIGHT JOIN syntax = FROM left_table RIGHT JOIN right_table ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left_table RIGHT JOIN right_table ON condition [OK]

Common Mistakes:

• Placing JOIN keyword incorrectly
• Using WHERE instead of ON for join condition
• Misordering table names

3. Given these tables:

employees
id | name
1 | Alice
2 | Bob
3 | Carol

departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.id = departments.id ORDER BY employees.id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B. [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Understand LEFT JOIN with given tables

   LEFT JOIN keeps all employees. For each employee, it tries to find a department with matching id.

2. Step 2: Match rows and fill NULLs

   Alice (id=1) has no matching department, so dept_name is NULL. Bob (id=2) matches Sales. Carol (id=3) matches HR.

3. Final Answer:

   [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}] -> Option C

4. Quick Check:

   LEFT JOIN keeps all employees, unmatched dept_name is NULL [OK]

Hint: LEFT JOIN keeps all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Mixing up LEFT and RIGHT JOIN results
• Ignoring NULL values for unmatched rows

4. You wrote this query but it gives an error:

SELECT * FROM employees RIGHT JOIN departments employees.id = departments.id;

What is the error and how to fix it?

medium

A. RIGHT JOIN is not supported; use LEFT JOIN instead

B. Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id

C. Table names are reversed; switch employees and departments

D. Use WHERE instead of ON for join condition

Solution

1. Step 1: Identify syntax error in JOIN clause

   The query is missing the ON keyword before the join condition, which is required syntax.

2. Step 2: Correct the syntax

   Add ON before employees.id = departments.id to fix the error.

3. Final Answer:

   Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id -> Option B

4. Quick Check:

   JOIN condition must use ON keyword [OK]

Hint: JOIN conditions always need ON keyword before condition [OK]

Common Mistakes:

• Omitting ON keyword
• Using WHERE instead of ON for join condition
• Confusing table order in JOIN

5. You want to list all departments and their employees, including departments with no employees. Which query achieves this?

hard

A. SELECT departments.dept_name, employees.name FROM employees RIGHT JOIN departments WHERE employees.id = departments.id;

B. SELECT departments.dept_name, employees.name FROM employees LEFT JOIN departments ON employees.id = departments.id;

C. SELECT departments.dept_name, employees.name FROM departments RIGHT JOIN employees ON departments.id = employees.id;

D. SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id;

Solution

1. Step 1: Identify which table to keep all rows from

   We want all departments, even those without employees, so departments is the left table in LEFT JOIN.

2. Step 2: Use LEFT JOIN from departments to employees

   LEFT JOIN keeps all departments and adds matching employees or NULL if none.

3. Final Answer:

   SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id; -> Option D

4. Quick Check:

   LEFT JOIN from departments keeps all departments [OK]

Hint: LEFT JOIN from main table keeps all its rows, unmatched get NULLs [OK]

Common Mistakes:

• Using employees as left table instead of departments
• Using RIGHT JOIN incorrectly
• Confusing which table to keep all rows from