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.
- Inner join
- Left join
- Right join
- Full join
Create sample tables
We need to create at least two tables to lean how join works. Therefore, create two tables
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 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 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 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.