MySQL joins

A join is a mechanism that combines rows from two or more tables based on a related column between them. The purpose of using joins is to retrieve data from multiple tables in a single result set. The common columns used for joining tables are usually primary and foreign keys. There are several types of joins in MySQL:

consider the following database,

1. INNER JOIN:

Returns only the rows where there is a match in both tables based on the specified condition.

Syntax:

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output:

Explanation:

The SQL query selects employee_id, employee_name, and department_name from employees and departments tables, joining them on department_id. It retrieves information about employees and their corresponding department names.

2. LEFT (OUTER) JOIN:

Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Syntax:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Output:

Explanation:

The SQL query uses a LEFT JOIN to retrieve employee_id, employee_name, and department_name from employees and departments, showing all employees and their associated department names, including those without a match.

3. RIGHT (OUTER) JOIN:

Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

Explanation:

The SQL query employs a RIGHT JOIN to fetch employee_id, employee_name, and department_name from employees and departments, displaying all departments and their corresponding employees, including unmatched departments without employees.

4. FULL (OUTER) JOIN:

Returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the non-matching table.

Syntax:

SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

Example: in above database,

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Output:

Explanation:

The SQL query employs a FULL JOIN to retrieve employee_id, employee_name, and department_name from employees and departments, displaying all records from both tables, matching on department_id and including unmatched rows from both tables.

Now, when we have learned what is relationships in MySQL and what are MySQL joins. Let us see

MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes the Structured Query Language (SQL) to interact with databases, making it a popular choice for web applications and various software systems. MySQL’s versatility, reliability, and ease of use make it a preferred solution for developers and organizations seeking efficient data management capabilities.

In this article, you will learn about How to select the last records in a one-to-many relationship using MySQL join by using examples.