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.

Practice

1. What does the EXISTS keyword do in a SQL query?

easy

A. Checks if a subquery returns any rows and returns true or false

B. Counts the total number of rows in a table

C. Joins two tables based on a condition

D. Deletes rows from a table

Solution

1. Step 1: Understand the purpose of EXISTS

   The EXISTS keyword is used to check if a subquery returns any rows. It returns true if there is at least one row, otherwise false.

2. Step 2: Compare with other options

   Counting rows, joining tables, or deleting rows are different SQL operations unrelated to EXISTS.

3. Final Answer:

   Checks if a subquery returns any rows and returns true or false -> Option A

4. Quick Check:

   EXISTS = true if subquery has rows [OK]

Hint: EXISTS means: 'Is there at least one row?' [OK]

Common Mistakes:

• Confusing EXISTS with COUNT
• Thinking EXISTS joins tables
• Assuming EXISTS deletes data

2. Which of the following is the correct syntax to use EXISTS in a WHERE clause?

easy

A. WHERE EXISTS IN (SELECT * FROM orders WHERE customer_id = customers.id)

B. WHERE EXISTS SELECT * FROM orders WHERE customer_id = customers.id

C. WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id)

D. WHERE EXISTS = (SELECT * FROM orders WHERE customer_id = customers.id)

Solution

1. Step 1: Check correct EXISTS syntax

   The correct syntax requires EXISTS followed by a subquery enclosed in parentheses.

2. Step 2: Validate each option

   WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id) uses parentheses correctly. The other options have syntax errors or incorrect keywords.

3. Final Answer:

   WHERE EXISTS (SELECT * FROM orders WHERE customer_id = customers.id) -> Option C

4. Quick Check:

   EXISTS needs parentheses around subquery [OK]

Hint: EXISTS always followed by parentheses with a subquery [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Using '=' or 'IN' with EXISTS
• Writing subquery outside parentheses

3. Given tables customers(id, name) and orders(id, customer_id), what does this query return?

SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);

medium

A. All customers including those without orders

B. All customers who have placed at least one order

C. All orders with customer names

D. An error because of invalid subquery

Solution

1. Step 1: Understand the EXISTS subquery

   The subquery checks if there is at least one order with customer_id matching the customer's id.

2. Step 2: Determine the main query result

   The main query selects customer names only if the subquery returns rows, meaning the customer has orders.

3. Final Answer:

   All customers who have placed at least one order -> Option B

4. Quick Check:

   EXISTS filters customers with orders [OK]

Hint: EXISTS filters rows having matching related rows [OK]

Common Mistakes:

• Thinking it returns all customers
• Confusing with JOIN returning orders
• Assuming syntax error in subquery

4. Identify the error in this query:

SELECT id FROM customers WHERE EXISTS SELECT * FROM orders WHERE orders.customer_id = customers.id;

medium

A. Incorrect table alias usage

B. Using SELECT * instead of SELECT 1 in EXISTS

C. Missing WHERE clause in subquery

D. Missing parentheses around the subquery after EXISTS

Solution

1. Step 1: Check EXISTS syntax

   The EXISTS keyword must be followed by a subquery enclosed in parentheses.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery after EXISTS -> Option D

4. Quick Check:

   EXISTS requires parentheses around subquery [OK]

Hint: Always put parentheses after EXISTS [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Thinking SELECT * is invalid in EXISTS
• Confusing alias usage

5. You want to find all customers who have placed orders only in the last 30 days. Which query correctly uses EXISTS to achieve this?

A) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND order_date >= CURRENT_DATE - INTERVAL '30 days');
B) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id AND order_date <= CURRENT_DATE - INTERVAL '30 days');
C) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);
D) SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days');

hard

A. Select customers with orders in last 30 days using correct date filter

B. Select customers with orders older than 30 days

C. Select customers with any orders regardless of date

D. Select customers if any order in last 30 days ignoring customer link

Solution

1. Step 1: Understand the date filter requirement

   We want customers with orders placed in the last 30 days, so the order date must be >= current date minus 30 days.

2. Step 2: Analyze each query option

   Select customers with orders in last 30 days using correct date filter correctly filters orders by customer and date. Select customers with orders older than 30 days filters orders older than 30 days. Select customers with any orders regardless of date ignores date. Select customers if any order in last 30 days ignoring customer link ignores customer link.

3. Final Answer:

   Select customers with orders in last 30 days using correct date filter -> Option A

4. Quick Check:

   EXISTS with date filter and customer match [OK]

Hint: Add date condition inside EXISTS subquery with customer match [OK]

Common Mistakes:

• Using wrong date comparison operator
• Forgetting to link orders to customers
• Filtering orders without customer condition