DEV Community

Cover image for A Developer’s Guide to SQL NOT IN: Smarter Queries, Faster Results
DbVisualizer
DbVisualizer

Posted on

1

A Developer’s Guide to SQL NOT IN: Smarter Queries, Faster Results

The NOT IN clause in SQL is useful for filtering out unwanted data. But it comes with caveats—mainly around NULLs, subqueries, and scalability. Here’s a breakdown of how NOT IN behaves in real scenarios and how to use it safely.

Real-World Uses of NOT IN

These examples illustrate how NOT IN works in typical queries:

  • Excluding known values:
SELECT * FROM company.invoices
WHERE issued_by NOT IN ('Jack', 'Josh', 'Matthew');
Enter fullscreen mode Exit fullscreen mode
  • Using a subquery for exclusions:
SELECT username FROM demo_table
WHERE user_id NOT IN (SELECT id FROM demo_table2);
Enter fullscreen mode Exit fullscreen mode
  • Nested joins and subquery filtering:
SELECT * FROM purchases.suppliers
WHERE supplier NOT IN (
  SELECT supplier_id
  FROM old_purchases.suppliers
  INNER JOIN orders ON customers.customer_id = orders.customer_id
);
Enter fullscreen mode Exit fullscreen mode

These queries are clear-cut but don’t always scale well or behave consistently when nulls are present.

Performance Watchouts

NOT IN struggles in high-data environments or when queries become complex. Performance suffers when:

  • Datasets grow beyond a few million rows.
  • Subqueries involve joins and multiple filters.
  • NULL values are returned, affecting accuracy.
  • The database is not optimized for reads or lacks indexing.

Make sure to profile your queries using tools available in your SQL environment or a dedicated SQL client.

FAQ

What does NOT IN do?

It filters out rows that match values in a list or subquery.

What makes it problematic?

Large datasets, NULL values, and deeply nested logic can cause performance issues or unexpected results.

When is it safe to use?

When working with moderate-sized datasets that don’t include nulls in subqueries.

Why use a tool like DbVisualizer?

A solid SQL client helps visualize query plans, optimize performance, and prevent logic bugs. DbVisualizer supports most major databases.

Conclusion

SQL NOT IN is easy to implement but needs to be handled with care. It works best on clean, reasonably sized datasets where nulls and performance are not major concerns. As your database grows or your logic gets more complex, keep an eye on query speed and result accuracy.

Read out the article SQL NOT IN: the Good, Bad & the Ugly for more insights.

Google AI Education track image

Build Apps with Google AI Studio 🧱

This track will guide you through Google AI Studio's new "Build apps with Gemini" feature, where you can turn a simple text prompt into a fully functional, deployed web application in minutes.

Read more →

Top comments (0)

Feature flag article image

Create a feature flag in your IDE in 5 minutes with LaunchDarkly’s MCP server 🏁

How to create, evaluate, and modify flags from within your IDE or AI client using natural language with LaunchDarkly's new MCP server. Follow along with this tutorial for step by step instructions.

Read full post

👋 Kindness is contagious

Explore this practical breakdown on DEV’s open platform, where developers from every background come together to push boundaries. No matter your experience, your viewpoint enriches the conversation.

Dropping a simple “thank you” or question in the comments goes a long way in supporting authors—your feedback helps ideas evolve.

At DEV, shared discovery drives progress and builds lasting bonds. If this post resonated, a quick nod of appreciation can make all the difference.

Okay