MySQL Joins: INNER, OUTER, LEFT and RIGHT

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 Joins: INNER, OUTER, LEFT and RIGHT

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

reg_no name
1 John Doe
2 Jane Doe
10 Joe Doe

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.

name mark1 mark2
John Doe 80 95
John Doe 80 95

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;
name mark1 mark2
John Doe 80 95
John Doe 80 95
Joe Doe NULL NULL

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.

mark1 mark2 name
80 95 John Doe
80 95 Jane Doe
70 93 NULL
85 90 NULL
95 95 NULL
Share Tweet Send
0 Comments
Loading...
You've successfully subscribed to GeekInsta
Great! Next, complete checkout for full access to GeekInsta
Welcome back! You've successfully signed in
Success! Your account is fully activated, you now have access to all content.