Subqueries with EXISTS in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of EXISTS with a simple subquery

Given the tables employees and departments, what is the output of the following query?

SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = 'New York');

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, department_id INT);
CREATE TABLE departments (id INT, location TEXT);
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO departments VALUES (10, 'New York'), (20, 'Chicago');

A Alice, Charlie

B Bob

C Alice, Bob, Charlie

D No rows

💡 Hint

EXISTS returns true if the subquery finds any matching rows.

The subquery checks if the employee's department is located in New York. Only employees in department 10 qualify, which are Alice and Charlie.

❓ query_result

intermediate

Filtering with NOT EXISTS

What does this query return?

SELECT d.id FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, department_id INT);
CREATE TABLE departments (id INT);
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20);
INSERT INTO departments VALUES (10), (20), (30);

A 10, 20

B No rows

C 10, 20, 30

D 30

💡 Hint

NOT EXISTS returns true when the subquery finds no matching rows.

Department 30 has no employees, so it is returned by the query.

📝 Syntax

advanced

Identify the syntax error in EXISTS subquery

Which option contains a syntax error in the EXISTS subquery?

SELECT name FROM employees WHERE EXISTS (SELECT * FROM departments WHERE id = department_id);

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, department_id INT);
CREATE TABLE departments (id INT);
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20);
INSERT INTO departments VALUES (10), (20), (30);

A SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id);

B SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = employees.department_id);

C SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = employees.department_id AND );

D SELECT name FROM employees WHERE EXISTS (SELECT * FROM departments WHERE id = department_id);

💡 Hint

Look for incomplete conditions or misplaced syntax in the subquery.

Option C ends the WHERE clause with AND but no condition after it, causing a syntax error.

❓ optimization

advanced

Optimizing EXISTS subquery for performance

Which query is generally more efficient for checking existence of related rows?

Option 1:
SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = employees.department_id);

Option 2:
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments);

A Both have the same performance in all cases.

B Option 1 is more efficient because EXISTS stops at first match.

C Option 2 is more efficient because IN uses indexes better.

D Option 2 is more efficient because it returns all matches at once.

💡 Hint

EXISTS stops searching after finding the first matching row.

EXISTS is optimized to stop scanning once a match is found, making it faster for existence checks than IN which may scan all matches.

🧠 Conceptual

expert

Understanding EXISTS with correlated subqueries

Consider the query:

SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM projects p WHERE p.lead_id = e.id AND p.status = 'active');

What does this query return?

A Names of employees who lead at least one active project.

B Names of employees who have any project regardless of status.

C Names of employees who do not lead any active project.

D Names of all employees.

💡 Hint

EXISTS with a correlated subquery checks for matching rows per outer row.

The subquery checks if the employee leads any project with status 'active'. Only those employees are returned.