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.