Joins in MySQL

Relational databases store data in multiple related tables.
To combine related data from those tables, we use Joins.

In MySQL, joins allow you to retrieve meaningful information by connecting tables using related columns (usually primary and foreign keys).

Joins are one of the most important topics in SQL.

1. Why Joins Are Needed

Consider two tables:

Students

student_id

name

dept_id

1

Rahul

101

2

Anita

102

3

Aman

105

Departments

dept_id

dept_name

101

Computer

102

Electrical

If you want to display:

Student Name + Department Name

The department name is not stored in the Students table.

So you must combine both tables using a join.

Without joins, relational databases lose their power.

2. Basic JOIN Syntax

SELECT columns

FROM table1

JOIN table2

ON table1.column = table2.column;

The ON clause defines how the tables are related.

3. INNER JOIN

An INNER JOIN returns only matching rows from both tables.

Example

SELECT Students. name, Departments.dept_name

FROM Students

INNER JOIN Departments

ON Students.dept_id = Departments.dept_id;

Result:

  • Rahul → Computer
  • Anita → Electrical

Aman will not appear becausethe  dept_id 105 does not exist.

INNER JOIN returns only the intersection of both tables.

4. LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns:

  • All rows from the left table
  • Matching rows from the right table
  • NULL if no match exists

Example

SELECT Students. name, Departments.dept_name

FROM Students

LEFT JOIN Departments

ON Students.dept_id = Departments.dept_id;

Result:

  • Rahul → Computer
  • Anita → Electrical
  • Aman → NULL

LEFT JOIN keeps everything from the left table.

5. RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns:

  • All rows from the right table
  • Matching rows from the left table
  • NULL if no match exists

Example

SELECT Students .name, Departments.dept_name

FROM Students

RIGHT JOIN Departments

ON Students.dept_id = Departments.dept_id;

All departments will appear even if no student belongs to them.

6. FULL JOIN (Conceptual)

MySQL does not directly support FULL JOIN.

FULL JOIN means:

  • All rows from both tables
  • NULL where no match exists

It can be simulated using UNION:

SELECT Student s.name, Departments.dept_name

FROM Students

LEFT JOIN Departments

ON Students.dept_id = Departments.dept_id

 

UNION

 

SELECT Studen ts.name, Departments.dept_name

FROM Students

RIGHT JOIN Departments

ON Students.dept_id = Departments.dept_id;

Conceptually:

FULL JOIN = LEFT JOIN + RIGHT JOIN

7. CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables.

It pairs every row of table1 with every row of table2.

Syntax

SELECT *

FROM Students

CROSS JOIN Departments;

If:
Studehave has 3 rows
Departmhaves has 2 rows

Result = 3 × 2 = 6 rows

Each student is paired with every department.

Important:
If you forget the ON condition in a normal JOIN, you may accidentally create a cross join.

8. SELF Jself-joinF JOIN joins a table with itself.

Used when rows in the same table are related.

Example: Employees table with manager_id.

Employees

emp_id

name

manager_id

1

A

NULL

2

B

1

3

C

1

Query

SELECT e.name AS employee,

       m.name AS manager

FROM Employees e

LEFT JOIN Employees m

ON e.manager_id = m.emp_id;

Here:

  • e represents employee
  • m represents manager

Self joins require table aliases.

9. NATURAL JOIN (Use Carefully)

A NATURAL JOIN automatically joins tables based on columns with the same name.

SELECT *

FROM Students

NATURAL JOIN Departments;

It automatically joins on dept_id if both tables have that column.

Why beginners should avoid it:

  • It joins based on column names automatically.
  • If the schema changes, the results may change unexpectedly.
  • It reduces clarity.

Best practice:
Always use explicit JOIN with ON.

10. Multiple Table Joins

You can join more than two tables.

Example: Orders system

SELECT Customers. name,

       Products.product_name,

       Orders.order_date

FROM Orders

INNER JOIN Customers

ON Orders.customer_id = Customers.customer_id

INNER JOIN Products

ON Orders.product_id = Products.product_id;

Joins can be chained one after another.

11. Join vs Subquery

Both combine data from multiple tables.

Join:

  • Combines tables directly
  • Usually faster
  • More readable for relational queries

Subquery:

  • Query inside another query
  • Sometimes easier to understand

Example using subquery:

SELECT name

FROM Students

WHERE dept_id IN

(SELECT dept_id FROM Departments WHERE dept_name = 'Computer');

Same using JOIN:

SELECT Students .name

FROM Students

INNER JOIN Departments

ON Students.dept_id = Departments.dept_id

WHERE Departments.dept_name = 'Computer';

In most relational cases, JOIN is preferred.

12. Common Beginner Mistakes

  1. Forgetting the ON condition
    This creates a cross join.
  2. Joining othe n wrong columns
  3. Confusing LEFT JOIN with INNER JOIN
  4. Not using table aliases in self joins
  5. Not qualifying column names when tables share column names

Use:

Students.dept_id

Summary of Join Types

Join Type

What It Returns

INNER JOIN

Only matching rows

LEFT JOIN

All left + matching right

RIGHT JOIN

All right + matching left

FULL JOIN

All rows from both tables

CROSS JOIN

Cartesian product

SELF JOIN

Table joined with itself

NATURAL JOIN

Auto-join based on the same column names