EXISTS in PostgreSQL 🚀

The EXISTS operator in PostgreSQL is used to check whether a subquery returns any rows. It returns TRUE if the subquery has results, otherwise, it returns FALSE.

1. Basic Syntax

SELECT column1, column2 FROM table_name WHERE EXISTS (subquery);

• If the subquery returns at least one row, the main query runs.
• If the subquery returns no rows, the main query does nothing.

2. Example: Check If a User Has Orders

SELECT name FROM users WHERE EXISTS (    SELECT 1 FROM orders     WHERE orders.user_id = users.id);

🔹 This query returns only users who have placed orders.

3. Example: Check If a Table Exists

SELECT EXISTS (    SELECT FROM information_schema.tables     WHERE table_name = 'users');

🔹 Returns TRUE if the users table exists, otherwise FALSE.

4. Example: Insert Only If Data Exists

INSERT INTO premium_users (id, name, email)SELECT id, name, email FROM users WHERE EXISTS (    
SELECT 1 FROM orders WHERE orders.user_id = users.id);

🔹 This inserts users only if they have at least one order.

5. Example: Delete Rows If Condition Exists

DELETE FROM users WHERE EXISTS (    SELECT 1 FROM banned_users     WHERE banned_users.email = users.email);

🔹 Deletes users who exist in the banned_users table.

6. Example: Using NOT EXISTS

SELECT name FROM users WHERE NOT EXISTS (    SELECT 1 FROM orders     WHERE orders.user_id = users.id);

🔹 Returns users who have never placed an order.

7. Example: Check If a Column Exists Before Dropping

DO $$ BEGIN    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' 
AND column_name='age') THEN        ALTER TABLE users DROP COLUMN age;    END IF;END $$;

🔹 Drops the age column only if it exists.

8. Example: Check If a Row Exists Before Updating

UPDATE users SET status = 'VIP' WHERE EXISTS (    SELECT 1 FROM orders WHERE orders.user_id = users.id);

🔹 Updates users to "VIP" only if they have orders.

9. Performance Tip

• EXISTS is faster than IN for large datasets.
• Avoid EXISTS inside loops in your application code.

Next Steps

✅ Combine EXISTS with JOIN for efficiency
✅ Use EXISTS in transactions
✅ Optimize indexes for EXISTS queries