🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

Tools

HTML MASTER CLASS /// LEARN TAGS /// BUILD STRUCTURE /// SEMANTIC WEB /// HTML MASTER CLASS /// LEARN TAGS ///

EXISTS vs IN in SQL & Databases

Learn about EXISTS vs IN in this comprehensive SQL & Databases development tutorial. The Subquery battle.

⚡ Total XP: 0|💻 sql XP: 0

Skill Matrix

UNLOCK NODES BY LEARNING NEW TAGS.

Select an unlocked node to view details root

🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

1Performance difference

When using a subquery, IN will run the entire subquery first, load all the IDs into memory, and then filter. EXISTS stops executing the subquery the very microsecond it finds a single match. For massive tables, EXISTS is astronomically faster than IN.

2Step-by-Step Breakdown

Beyond Equality. We know '=' and '<>'. But SQL provides a rich set of operators specifically designed to make complex WHERE clauses easier to write and faster to execute.

LIKE vs ILIKE. 'LIKE' is case-sensitive in Postgres. If a user searches for 'bob', it won't find 'Bob'. Postgres offers 'ILIKE' for Insensitive pattern matching. (MySQL LIKE is insensitive by default).

Wildcards: % vs _. The '%' matches ANY number of characters. The '_' (underscore) matches EXACTLY ONE character. 'WHERE name LIKE 'B_b'' matches Bob or Bib, but not Boob.

The IN Operator. If you have an array of IDs from your frontend [1, 5, 9], you inject them into an IN clause. 'WHERE id IN (1, 5, 9)'. This is vastly cleaner than writing 'id=1 OR id=5 OR id=9'.

Knowledge Check. In PostgreSQL, if you want to search for a name but you don't care if the user typed it in uppercase or lowercase, which operator should you use?

→LIKE
→ILIKE

NOT IN. You can negate operators easily. 'WHERE status NOT IN ('banned', 'suspended');'. This fetches all active or pending users.

ANY and ALL. Used with subqueries. 'WHERE salary > ALL (SELECT salary FROM interns)'. This guarantees the fetched salary is higher than every single intern's salary.

EXISTS. Used to check if a subquery returns ANY data. 'WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)'. This is highly optimized for performance.

Date Comparisons. Operators work perfectly on timestamps. 'WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01''.

Summary. Use the right operator to keep your queries readable and fast.

Level Up 🚀

Advanced cheat sheets, SEO tricks, and interview prep for this topic.

Browser Support

ChromeSupported

Fully supported.

FirefoxSupported

Fully supported.

SafariSupported

Fully supported.

EdgeSupported

Fully supported.

Accessibility (A11y)

1Search Result Tables Need Accessible Empty States

A search UI built on LIKE/ILIKE should announce 'No results found for "bxb"' via an aria-live region when the query returns zero rows, instead of silently rendering an empty table that a screen reader user can't distinguish from a loading state.

SEO Implications

1
Slow LIKE Queries Delay Server-Rendered Search Pages
A leading-wildcard pattern like LIKE '%term%' cannot use a standard B-Tree index and forces a full table scan, which can slow down server-rendered search results pages enough to hurt Time to First Byte and Core Web Vitals.

Best Practices

Prefer EXISTS Over IN for Subqueries Against Large Tables

IN evaluates the entire subquery and holds every result in memory before filtering, while EXISTS stops as soon as it finds one matching row — on large tables this makes EXISTS dramatically faster.

Use ILIKE (or LOWER() Comparisons) for Case-Insensitive User Search

Plain LIKE is case-sensitive in PostgreSQL, so a search for 'bob' silently misses 'Bob' — use ILIKE, or normalize both sides with LOWER(), so user-facing search behaves the way users expect.

Frequent Bugs

THE BUG

A LIKE '%term%' search that used to be fast becomes painfully slow as the table grows past a few hundred thousand rows.

THE FIX

A leading wildcard prevents the database from using a standard index, forcing a sequential scan of every row. For real full-text search, use a dedicated index type like PostgreSQL's GIN/trigram index instead of a plain LIKE pattern.

Real-World Examples

Filtering Orders by a List of IDs from the Frontend

A frontend sent an array of selected order IDs [12, 45, 89] to bulk-approve, and the backend needed to match all of them in a single query instead of looping.

SELECT * FROM orders WHERE id IN (12, 45, 89) AND status <> 'cancelled';

Interview Prep

Pascual Vila

Frontend Instructor // Code Syllabus

Common Pitfalls & Errors

The Error //

Building a LIKE pattern by directly concatenating user input

// Wrong
const query = `SELECT * FROM users WHERE name LIKE '%${searchTerm}%'`;

// Correct
const query = 'SELECT * FROM users WHERE name ILIKE $1';
client.query(query, [`%${searchTerm}%`]);

The Solution //

Concatenating raw user input into a LIKE pattern string (rather than passing it as a bound parameter) reopens the door to SQL injection, since a user typing % or a quote character can alter the query's meaning. Always pass the search term as a parameter and build the wildcard pattern in the parameter value, not the query text.

The Error //

Using LIKE for case-insensitive search and getting incomplete results

-- Wrong: misses 'Bob', 'BOB', etc.
SELECT * FROM users WHERE name LIKE 'bob%';

-- Correct
SELECT * FROM users WHERE name ILIKE 'bob%';

The Solution //

Plain LIKE is case-sensitive in PostgreSQL, so a search for 'bob' will not match a row stored as 'Bob'. Use ILIKE for a case-insensitive comparison, or normalize both sides with LOWER() if you need cross-database compatibility.