PostgreSQL EXISTS Operator

The EXISTS operator in PostgreSQL is used to test whether a subquery returns any rows.
It returns TRUE if the subquery returns at least one row, otherwise FALSE.

🔹 EXISTS checks for existence, not actual values.

1️⃣ Basic EXISTS Syntax

2️⃣ Simple EXISTS Example

Tables:

users

id	name
1	Amit
2	Riya

orders

id	user_id	amount
101	1	500

Query: find users who have placed at least one order.

✔ Returns Amit only.

3️⃣ NOT EXISTS Example

Find users who have not placed any orders.

✔ Returns Riya.

4️⃣ EXISTS vs IN

Using IN

Using EXISTS (Better for large data)

✔ EXISTS stops checking once a match is found
✔ Safer when subquery contains NULLs

5️⃣ EXISTS with Multiple Conditions

✔ Users with high-value orders.

6️⃣ EXISTS with DELETE

7️⃣ EXISTS with UPDATE

8️⃣ EXISTS vs JOIN (Conceptual Difference)

EXISTS	JOIN
Checks presence	Combines rows
Returns TRUE/FALSE	Returns data
Faster for existence check	Better for data retrieval

9️⃣ Common Mistakes

❌ Selecting columns inside EXISTS
✔ Use SELECT 1 or SELECT *

❌ Forgetting correlation condition
✔ Always link subquery with outer query

🔟 Best Practices

✔ Use EXISTS for existence checks
✔ Prefer EXISTS over IN for large tables
✔ Index columns used in EXISTS condition
✔ Use NOT EXISTS instead of LEFT JOIN IS NULL

PostgreSQL EXISTS Operator