Subqueries with EXISTS in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL keyword EXISTS do in a query?

EXISTS checks if a subquery returns any rows. If it does, the condition is true; if not, it is false.

intermediate

How does a subquery with EXISTS differ from a normal join?

A subquery with EXISTS only checks for the presence of rows, not the actual data. A join combines rows from two tables based on a condition.

beginner

Write a simple example of a query using EXISTS to find customers who have orders.

SELECT customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

beginner

Why is SELECT 1 often used inside an EXISTS subquery?

Because EXISTS only cares if rows exist, not the data itself. SELECT 1 is simple and efficient.

intermediate

Can EXISTS subqueries improve performance compared to joins? Why or why not?

Yes, sometimes. EXISTS stops searching as soon as it finds a matching row, which can be faster than joining all rows.

What does the EXISTS keyword check in a SQL query?

A If the main query has any rows

B If the subquery returns any rows

C If the subquery returns a specific column value

D If the tables are joined correctly

EXISTS checks if the subquery returns any rows, making the condition true if yes.

Which of the following is a valid use of EXISTS?

A SELECT * FROM table WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

B SELECT * FROM table WHERE EXISTS = 5;

C SELECT * FROM table WHERE EXISTS table.column;

D SELECT * FROM table WHERE EXISTS (table.column = 1);

Option A correctly uses EXISTS with a subquery.

What will this query return?<br>

SELECT name FROM students s WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

A Students who have at least one enrollment

B All students regardless of enrollment

C Enrollments without students

D Students with no enrollments

The query returns students who have at least one matching enrollment.

Why might SELECT 1 be preferred inside an EXISTS subquery?

A Because it counts the rows

B Because it returns the first row only

C Because <strong>EXISTS</strong> ignores the selected columns

D Because it filters rows

EXISTS only checks for row existence, so the actual column selected does not matter.

Which is true about EXISTS subqueries?

A They return all rows from the subquery

B They always return false

C They join tables like INNER JOIN

D They return true if the subquery has any rows

EXISTS returns true if the subquery returns at least one row.

Explain how the EXISTS keyword works in SQL and give a simple example.

Think about checking if something exists rather than retrieving data.

Describe a situation where using EXISTS might be better than using a JOIN.

Consider when you only want to know if related data exists.