SQL EXISTS vs JOIN: Find Customers Who Have Placed an Order

SQL EXISTS vs JOIN: Find Customers Who Have Placed an Order

Imagine your manager asks you to find all customers who have placed at least one order.

You have two tables. Some customers have placed multiple orders, while others haven't placed any.

How would you write this query?

Let's look at the data.

1. Sample Tables

customers

cust_id| name| city
101| Amit| Pune
102| Neha| Mumbai
103| Rahul| Pune
104| Priya| Delhi

orders

order_id| cust_id| amount
501| 101| 5000
502| 101| 3000
503| 103| 7000
504| 103| 2000

Notice that Amit and Rahul have placed two orders each. Neha and Priya haven't placed any orders.

1. Find Customers Who Have Placed an Order

One way to solve this problem is by using the "EXISTS" operator.

SELECT c.cust_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.cust_id = c.cust_id
);

1. Output

cust_id| name
101| Amit
103| Rahul

Only Amit and Rahul are returned.

1. How Does EXISTS Work?

The database checks each customer against the orders table.

• Amit: A matching order exists, so he is included.
• Neha: No matching order exists, so she is excluded.
• Rahul: A matching order exists, so he is included.
• Priya: No matching order exists, so she is excluded.

Even though Amit and Rahul have multiple orders, each customer appears only once.

That's because "EXISTS" checks whether at least one matching record is available. It doesn't return the matching order records.

1. What Happens If We Use JOIN?

We can also write the query using an INNER JOIN.

SELECT c.cust_id, c.name
FROM customers c
JOIN orders o
ON c.cust_id = o.cust_id;

The result will be:

cust_id| name
101| Amit
101| Amit
103| Rahul
103| Rahul

Why do we get four rows?

Because each matching order produces a row in the JOIN result.

If a customer has 10 orders, that customer can appear 10 times.

We could use "DISTINCT" to remove duplicate customers:

SELECT DISTINCT c.cust_id, c.name
FROM customers c
JOIN orders o
ON c.cust_id = o.cust_id;

This returns the same two customers as the EXISTS query.

1. EXISTS vs JOIN: Which One Should You Use?

Use "EXISTS" when you only need to check whether a matching record is available.

Use "JOIN" when you need columns from both tables or want to combine related records.

Both approaches are useful. The right choice depends on what information you need from the query.

1. Want to Learn More About SQL Subqueries?

If you want to understand SQL subqueries better, including "EXISTS", "NOT EXISTS", and "IN", have a look at this practical guide:

"SQL Subqueries Explained with Examples – Sankalan Data Tech" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/subqueries-solve-queries-sql-faq.html)

It covers useful subquery concepts with practical SQL examples.

You can also explore more SQL and PL/SQL tutorials at "Sankalan Data Tech" (https://www.sankalandtech.com/).

1. One Question for You

What would happen if Amit placed 10 more orders?

Would he appear 11 times in the output when using "EXISTS"?

Think about it before checking your answer.

Answer: No. Amit would still appear only once.

---

Follow Sankalan Data Tech for more practical SQL examples, database concepts, and interview challenges.

sql #database #programming #beginners