DEV Community

Cover image for IN vs EXISTS in SQL: Understanding Performance and Usage
MD ARIFUL HAQUE
MD ARIFUL HAQUE

Posted on

1

IN vs EXISTS in SQL: Understanding Performance and Usage

IN vs EXISTS in MySQL: A Hands-on Example and Description

In MySQL, both IN and EXISTS are used in queries to filter data based on the presence of rows in a subquery. However, they work in different ways, and choosing between them can impact query performance. Let’s break down their differences with explanations and hands-on examples.


1. IN Clause

  • Description:
    The IN clause is used to filter rows based on whether a column's value matches any value in a list or a subquery. It checks for matching values from the inner query and compares them against the outer query.

  • Performance:
    The IN clause is generally efficient when the subquery returns a small number of records. However, if the subquery returns a large dataset, IN can become slower.

  • Syntax:

  SELECT columns 
  FROM table 
  WHERE column IN (subquery);
Enter fullscreen mode Exit fullscreen mode

2. EXISTS Clause

  • Description:
    The EXISTS clause checks for the existence of rows returned by a subquery. If the subquery returns any row, EXISTS evaluates to TRUE and the outer query proceeds. It doesn’t care about the content of the rows but only whether the rows exist.

  • Performance:
    EXISTS is typically faster for large datasets since it stops processing once it finds a match. This makes it efficient when working with subqueries that return many rows.

  • Syntax:

  SELECT columns 
  FROM table 
  WHERE EXISTS (subquery);
Enter fullscreen mode Exit fullscreen mode

Hands-on Example

Let’s consider two tables: customers and orders.

customers Table:

customer_id customer_name
1 John Doe
2 Jane Smith
3 Alice Brown

orders Table:

order_id customer_id order_total
1 1 200
2 1 150
3 2 300

We want to find all customers who have placed at least one order.


Using the IN Clause

SELECT customer_name 
FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • The subquery (SELECT customer_id FROM orders) returns all customer IDs that appear in the orders table.
  • The outer query selects customers whose customer_id is in that result set.

Result:
| customer_name |
|---------------|
| John Doe |
| Jane Smith |


Using the EXISTS Clause

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

Explanation:

  • The subquery SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id checks whether any row in the orders table matches the customer_id of the current row from the customers table.
  • If any match is found, EXISTS returns TRUE, and the customer is included in the result.

Result:
| customer_name |
|---------------|
| John Doe |
| Jane Smith |


Key Differences

  1. Return Values:

    • IN: Compares the values of a column with the result set of the subquery.
    • EXISTS: Returns TRUE or FALSE based on whether the subquery returns any rows.
  2. Efficiency:

    • IN is more efficient for smaller datasets.
    • EXISTS is faster for large datasets, especially when the subquery returns many rows.
  3. Use Case:

    • Use IN when you're comparing a column’s value against a small list of possible values.
    • Use EXISTS when you're checking for the presence of rows in a subquery (e.g., when there's a correlation between the outer and inner queries).

Performance Example

Assume we have:

  • 10,000 customers
  • 100,000 orders

Query with IN:

SELECT customer_name 
FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders);
Enter fullscreen mode Exit fullscreen mode
  • Execution: MySQL will retrieve the entire result set from the subquery and compare it with each row in the outer query.

Query with EXISTS:

SELECT customer_name 
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Enter fullscreen mode Exit fullscreen mode
  • Execution: MySQL will check each row in the outer query and stop once it finds a matching row in the subquery, making it faster for large datasets.

Conclusion

  • Use IN when you have a simple list to compare or a small subquery result.
  • Use EXISTS when you’re dealing with large datasets or need to check for the presence of related data in a subquery.

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay