Subquery vs JOIN

You often need to retrieve data from multiple tables when working with SQL. JOINs and subqueries are key techniques for this, but they serve different purposes.

• JOINs directly combine rows from multiple tables based on a related column, producing a flat result set.
• Subqueries use a nested query to fetch data from one table and then use that result in the outer query, often for filtering or additional logic.

The best choice depends on the scenario. Let's explore when to use each with real-world examples.

---

Fetching Customer Orders

Imagine you run an online store. You have a Customers table and an Orders table. You want to see which customers have placed orders.

In this case, we use a JOIN because we need to combine data from two related tables.

Customers

Orders

Query Using JOIN

SELECT c.first_name, c.last_name, o.order_id, o.order_date
FROM Customers AS c
INNER JOIN Orders AS o 
ON c.customer_id = o.customer_id;

---

Finding Customers Without Orders

Now, let's say you need a list of customers who haven't placed an order yet.

In this case, you can use a subquery to check if a customer ID exists in the Orders table.

Query Using Subquery

SELECT *  
FROM Customers  
WHERE customer_id NOT IN (  
  SELECT customer_id  
  FROM Orders  
);

In this example, the subquery is used to calculate which customer IDs are associated with orders, and then the outer query filters out those customers, giving us only the customers who have not placed any orders.

---

Comparing JOIN vs. Subquery

---

Which One Should You Use?

• Use JOINS when you need to pull related data from multiple tables.
• Use subqueries when you need to filter or calculate before retrieving data.
• If performance is a concern, JOINS are usually faster than subqueries.

By understanding both techniques, you can write efficient, optimized SQL queries that best fit your data needs.