MySQL joins

MySQL joins are used to combine rows from two or more tables based on a related column between them. Joins are essential for retrieving data from multiple tables in a single query. In this article, we will discuss different types of joins in MySQL with examples.

Types of Joins in MySQL:

1. INNER JOIN: An inner join returns only the matching rows from both tables. It returns the rows that have matching values in both tables.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider two tables employees and departments:

Table: employees

Table: departments

To join these tables based on dept_id column:

SELECT employees.emp_name, departments.dept_name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id;

Output:

2. LEFT JOIN: A left join returns all the rows from the left table and matching rows from the right table. If there is no matching row in the right table, it returns null.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider two tables students and grades:

Table: students

Table: grades

To join these tables based on student_id column:

SELECT students.student_name, grades.grade
FROM students
LEFT JOIN grades
ON students.student_id = grades.student_id;

Output:

3. RIGHT JOIN: A right join returns all the rows from the right table and matching rows from the left table. If there is no matching row in the left table, it returns null.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider two tables employees and departments:

Table: employees