MySQL joins are used to fetch data from multiple tables. We can call it as a method for fetching records from one or more tables based on some common values in the table.

MySQL supports the following joins.

1. Inner join
2. Left join
3. Right join
4. Full join

Create sample tables

We need to create at least two tables to lean how join works. Therefore, create two tables students and marks and insert some data to it.

CREATE TABLE mydb.students(
	reg_no INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE mydb.marks(
	id INT AUTO_INCREMENT PRIMARY KEY,
    reg_no INT NOT NULL,
    mark1 INT,
    mark2 INT
);

Insert some records to the table.

INSERT INTO `students` (`reg_no`, `name`) VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(10, 'Joe Doe');

INSERT INTO `marks` (`id`, `reg_no`, `mark1`, `mark2`) 
VALUES (NULL, '1', '80', '95'), 
(NULL, '2', '80', '95'),
(NULL, '3', '70', '93'),
(NULL, '4', '850', '90'),
(NULL, '5', '95', '95');

The tables will be:

1) Students table

2) Marks table

Inner join

Inner join compares each rows of two or more tables. If values in both rows satisfy a given condition, the rows are returned.

SELECT students.name, marks.mark1, marks.mark2 FROM students
INNER JOIN
marks ON marks.reg_no = students.reg_no;

Executing this query will display the following output.

Note that only the columns (mark1 and mark2) of rows in marks table that has matching values for reg_no field in students table are selected.

Left join

Left join selects all rows from the left table even if there are no matching records in the right table. If there are no columns in the right table that satisfies the given condition, NULL is used in the columns from the right table.

SELECT students.name, marks.mark1, marks.mark2 FROM students
LEFT JOIN
marks ON marks.reg_no = students.reg_no;

Right join

Right join is similar to Left join. The only difference is that the Right join selects all records from the right table and all matching records from the left table. If no matching values are found in the left table, the columns for left table is filled with NULL. Here's an example.

SELECT marks.mark1, marks.mark2, students.name from students
RIGHT JOIN 
marks ON students.reg_no = marks.reg_no;

This query will display the following output.