• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Joins

← Previous Next →

In most databases, data is stored across multiple tables to keep it organized and avoid duplication. For example, customer details might be in one table while their orders are stored in another. But when you want to see complete information—like which customer placed which order—you need to combine data from both tables.

That’s where MySQL Joins come into play. A join lets you fetch related data from two or more tables in a single query.

What Is a Join in MySQL?

A JOIN in MySQL is used to combine rows from two or more tables based on a related column between them. Usually, this related column is a primary key in one table and a foreign key in another.

For example:

  • The customers table may have a column customer_id.

  • The orders table also has a column customer_id that refers to the customer who placed the order.

By joining these two tables, you can get complete information about which customer placed which order.

Why Use Joins in MySQL?

Joins make it possible to retrieve meaningful information from multiple tables without duplicating data. You can:

  • Fetch related data stored in different tables.

  • Keep your database normalized and efficient.

  • Build reports that connect customers, products, orders, or employees.

  • Perform complex queries with simple SQL commands.

Without joins, you’d have to make multiple queries and merge the results manually, which is time-consuming and inefficient.

Types of Joins in MySQL

MySQL supports several kinds of joins, each designed to handle a specific type of data relationship. The main types are:

  1. INNER JOIN

  2. LEFT JOIN (LEFT OUTER JOIN)

  3. RIGHT JOIN (RIGHT OUTER JOIN)

  4. CROSS JOIN

  5. SELF JOIN

  6. UNION

Let’s go through each briefly before learning them in detail in upcoming tutorials.

1. INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables.
If a record exists in one table but not in the other, it won’t appear in the result.

Example:

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Output:
This query will list customers who have placed at least one order. Customers without orders will not be shown.

2. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all records from the left table and the matching records from the right table. If there’s no match, the result will still include the left table’s data, but the right table’s columns will contain NULL.

Example:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Output:
This will show all customers. If a customer hasn’t placed any order, the order_id will appear as NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN works the opposite way. It returns all rows from the right table and the matching rows from the left table. If no match is found, the left table’s columns will contain NULL.

Example:

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

Output:
This shows all orders, even if a matching customer record doesn’t exist.

4. CROSS JOIN

A CROSS JOIN returns the Cartesian product of both tables — meaning every row from the first table is combined with every row from the second.

Example:

SELECT customers.customer_name, products.product_name
FROM customers
CROSS JOIN products;

Output:
If there are 5 customers and 4 products, the result will contain 20 rows (5 × 4).
CROSS JOIN is rarely used but can be useful when generating combinations or test data.

5. SELF JOIN

A SELF JOIN is a join where a table is joined with itself. It’s often used to compare rows within the same table.

Example:

SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;

Output:
This query shows each employee along with their manager’s name. The same table is used twice but referenced with different aliases.

6. UNION

While not technically a join, UNION is often discussed with joins because it also combines data. The UNION operator merges the results of two or more SELECT queries into a single result set.

Example:

SELECT customer_name FROM customers_2024
UNION
SELECT customer_name FROM customers_2025;

Output:
This merges customer names from both tables and removes duplicates automatically.

Example Scenario

Let’s look at how different joins behave with simple data.

Customers Table

customer_id customer_name
1 Ananya
2 Priya
3 Isha

Orders Table

order_id customer_id amount
101 1 500
102 2 700

Now, if you run different joins:

Type Result
INNER JOIN Shows Ananya and Priya only (both have orders).
LEFT JOIN Shows all customers (Ananya, Priya, Isha). For Isha, order columns are NULL.
RIGHT JOIN Shows all orders. If any order had no matching customer, that customer name would be NULL.
CROSS JOIN Shows every possible combination of customers and orders.

Performance Tips for Joins

When your database grows large, joins can become heavy on performance. Here are some practical tips:

  • Use Indexes:
    Make sure the columns used in the ON condition (like customer_id) are indexed.

  • Match Data Types:
    Both join columns should have the same data type to avoid unnecessary conversions.

  • Avoid Unnecessary Joins:
    Joining too many large tables can slow down queries. Only join what you really need.

  • Use EXPLAIN:
    Before executing complex queries, use the EXPLAIN keyword to check how MySQL will execute your query.

Common Mistakes to Avoid

  1. Forgetting the ON Clause
    If you forget to specify the ON condition, MySQL creates a CROSS JOIN, which multiplies all rows from both tables.

  2. Using the Wrong Join Type
    Always be clear about what you want:

    • Only matching rows → INNER JOIN

    • All records from one table → LEFT JOIN or RIGHT JOIN

  3. Ignoring NULLs
    Remember, LEFT and RIGHT JOIN can produce NULL values where no match exists. Handle them properly in your queries or reports.

When to Use Which Join

Situation Recommended Join
Show only matching records INNER JOIN
Show all from left table LEFT JOIN
Show all from right table RIGHT JOIN
Show all possible combinations CROSS JOIN
Compare rows in the same table SELF JOIN
Combine query results UNION

Summary of the Tutorial

MySQL Joins help you combine data from multiple tables in a clean, structured way. Whether you’re building reports, analyzing customer activity, or connecting product and sales data, joins are essential for meaningful results.

Understanding how each type works—INNER, LEFT, RIGHT, CROSS, SELF, and UNION—gives you full control over how your data is retrieved and displayed. Once you’re comfortable with joins, you’ll be able to write advanced SQL queries that handle real-world data efficiently.

Practice Questions

  1. Write an SQL query to display all customers who have placed at least one order using the INNER JOIN clause.

  2. Write a query to show all customers and their orders, including customers who haven’t placed any order yet, using a LEFT JOIN.

  3. Write a query that lists all orders and their corresponding customer names, including orders that don’t have a matching customer record, using a RIGHT JOIN.

  4. Create a query to display every possible combination of customers and products using a CROSS JOIN.

  5. Write a query to show each employee’s name along with their manager’s name from an employees table using a SELF JOIN.

  6. Suppose there are two tables students_2024 and students_2025. Write a query to combine all student names from both tables without duplicates using UNION.

  7. Write a query to display customer names, order IDs, and order amounts, but only for those who actually placed an order (exclude customers with no orders).

  8. Modify your LEFT JOIN query so that instead of showing NULL, it displays the text 'No Orders Yet' for customers who haven’t placed any orders.

  9. Write a query using INNER JOIN to list all products purchased by customers, showing both customer_name and product_name.

  10. Create a query using RIGHT JOIN to display all orders and their matching customer names, sorted by order_id in descending order.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top