MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS Great 2025

What are MySQL JOINS?

MySQL JOINS Tutorial: JOINS in MySQL allow you to retrieve data from multiple tables based on a related column. They help combine rows from different tables using Primary Keys (PKs) and Foreign Keys (FKs).

Why Use JOINS?

• Helps retrieve related data from multiple tables in a single query.
• Improves performance compared to running multiple queries.
• Avoids redundant data storage and improves database efficiency.

---

📌 Types of MySQL JOINS

MySQL JOINS Tutorial: MySQL supports different types of JOINS:

JOIN Type	Description	Returns
INNER JOIN	Matches rows from both tables where a condition is met	Only matching rows
LEFT JOIN	Returns all rows from the left table & matching rows from the right	All left + matched right rows
RIGHT JOIN	Returns all rows from the right table & matching rows from the left	All right + matched left rows
OUTER JOIN	Returns all records, filling missing matches with NULLs	All rows from both tables
CROSS JOIN	Combines all rows from both tables (Cartesian Product)	All possible row combinations

---

📌INNER JOIN – Fetching Only Matching Data

MySQL JOINS Tutorial: Use Case: Get a list of members who have rented movies, along with the movie titles.

sql

SELECT members.first_name, members.last_name, movies.title  

FROM members  

INNER JOIN movies  

ON members.movie_id = movies.id;

✔️ How It Works:

• The INNER JOIN returns only matching rows where movie_id in members matches id in movies.

Sample Output:

first_name	last_name	title
Adam	Smith	Assassin’s Creed
Ravi	Kumar	Real Steel
Susan	Davidson	Safe

---

📌 LEFT JOIN – Keeping All Left Table Data

MySQL JOINS Tutorial: Use Case: Get a list of all movies, showing the member who rented each one (if any).

sql

SELECT movies.title, members.first_name, members.last_name  

FROM movies  

LEFT JOIN members  

ON movies.id = members.movie_id;

✔️ How It Works:

• Returns all movies from movies table.
• If a movie was not rented, it returns NULL for the member details.

Sample Output:

title	first_name	last_name
Assassin’s Creed	Adam	Smith
Real Steel	Ravi	Kumar
Alvin & Chipmunks	NULL	NULL

Notice: “Alvin & Chipmunks” wasn’t rented, so member details are NULL.

---

📌 RIGHT JOIN – Keeping All Right Table Data

Use Case: Get a list of all members, including those who haven’t rented any movie.

sql

SELECT members.first_name, members.last_name, movies.title  

FROM members  

RIGHT JOIN movies  

ON members.movie_id = movies.id;

✔️ How It Works:

• Returns all members from members table.
• If a member didn’t rent any movie, movie title will be NULL.

Sample Output:

first_name	last_name	title
Adam	Smith	Assassin’s Creed
Ravi	Kumar	Real Steel
NULL	NULL	Alvin & Chipmunks

Notice: The last row has NULL values for first_name and last_name, meaning no member rented that movie.

---

📌 OUTER JOIN – Combining LEFT and RIGHT JOIN

Use Case: Get a list of all movies and members, including unmatched records.

MySQL does NOT support FULL OUTER JOIN directly, but you can achieve it by combining LEFT and RIGHT JOIN using UNION:

sql

MySQL JOINS Tutorial: SELECT movies.title, members.first_name, members.last_name  

FROM movies  

LEFT JOIN members  

ON movies.id = members.movie_id  

UNION  

MySQL JOINS Tutorial: SELECT movies.title, members.first_name, members.last_name  

FROM movies  

RIGHT JOIN members  

ON movies.id = members.movie_id;

✔️ How It Works:

• LEFT JOIN returns all movies with matching members.
• RIGHT JOIN returns all members with matching movies.
• UNION ensures both datasets are combined without duplicates.

---

📌 CROSS JOIN – Cartesian Product of Two Tables

MySQL JOINS Tutorial: Use Case: Get every possible combination of members and movies.

sql

SELECT members.first_name, members.last_name, movies.title  

FROM members  

CROSS JOIN movies;

✔️ How It Works:

• Combines each row from members with every row from movies.
• Results in a large dataset (n × m rows).

Sample Output (Partial):

first_name	last_name	title
Adam	Smith	Assassin’s Creed
Adam	Smith	Real Steel
Ravi	Kumar	Assassin’s Creed
Ravi	Kumar	Real Steel

Warning: CROSS JOINs can generate huge datasets if both tables are large.

---

📌 “ON” vs “USING” in JOINS

🔹 MySQL JOINS Tutorial: Using ON: Matches tables based on conditions, even with different column names.

sql

SELECT members.first_name, movies.title  

FROM members  

INNER JOIN movies  

ON members.movie_id = movies.id;

🔹 Using USING: Matches tables based on a common column name.

sql

SELECT members.first_name, movies.title  

FROM members  

INNER JOIN movies  

USING (movie_id);

Key Difference:

• ON works with any column names.
• USING works only if column names are the same in both tables.

---

📌 Why Use JOINS Instead of Multiple Queries?

✔️MySQL JOINS Tutorial: Performance: Faster than running multiple queries.
✔️ Less Data Transfer: Reduces load on the database.
✔️ Optimized Execution: MySQL uses indexing for efficient joins.
✔️ Simplifies Queries: A single JOIN query does the work of multiple queries.

---

📌 Summary – Key Takeaways

🔹 JOINS combine data from multiple tables in a single result set.
🔹 INNER JOIN – Returns only matching rows.
🔹 LEFT JOIN – Returns all left table rows + matching right table rows.
🔹 RIGHT JOIN – Returns all right table rows + matching left table rows.
🔹 OUTER JOIN (FULL JOIN) – Combines LEFT and RIGHT JOIN using UNION.
🔹 CROSS JOIN – Returns all possible row combinations.
🔹 Use ON for different column names and USING for identical column names.

Mastering MySQL JOINS helps you write efficient and optimized queries for complex data retrieval!

MYSQL UNION

Download My SQL