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