Subqueries vs JOINs comparison in MySQL

Introduction

We use subqueries and JOINs to get data from multiple tables. They help us combine information in different ways to answer questions.

When you want to find related data from two tables, like customers and their orders.

When you need to filter results based on data in another table.

When you want to simplify complex queries by breaking them into smaller parts.

When you want to improve query performance by choosing the best method.

When you want to write clear and easy-to-understand queries.

Syntax

MySQL

SELECT columns FROM table1
JOIN table2 ON table1.column = table2.column;

-- OR --

SELECT columns FROM table1
WHERE column IN (SELECT column FROM table2 WHERE condition);

JOIN combines rows from two tables based on a related column.

Subquery is a query inside another query, used to filter or calculate values.

Examples

This JOIN example gets customer names with their order IDs by matching customer IDs.

MySQL

SELECT customers.name, orders.id
FROM customers
JOIN orders ON customers.id = orders.customer_id;

This subquery finds customers who have placed orders by checking if their ID is in the orders table.

MySQL

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

This subquery finds the customer who made order 101.

MySQL

SELECT name FROM customers
WHERE id = (SELECT customer_id FROM orders WHERE id = 101);

Sample Program

This example creates two tables: customers and orders. It shows how to get customer names with their orders using JOIN and how to find customers who placed orders using a subquery.

MySQL

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1);

-- Using JOIN
SELECT customers.name, orders.id AS order_id
FROM customers
JOIN orders ON customers.id = orders.customer_id
ORDER BY customers.name;

-- Using Subquery
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders)
ORDER BY name;

Important Notes

JOINs are usually faster for large datasets because they combine tables directly.

Subqueries can be easier to read for simple filtering but might be slower.

Use JOINs when you need columns from both tables in the result.

Summary

JOINs combine tables side-by-side using matching columns.

Subqueries run inside another query to filter or find values.

Choose JOINs for performance and subqueries for simplicity in some cases.