Why MySQL Joins are used?
When we want to combine the data from two or more tables, we can use MySQL Joins by using the common column between the tables.
We already have the students table created in MySQL SELECT Statement now we will create a new table for student_report where we have marks for each subject for each student.
Let’s create a new table as shown below:
Create query:
CREATE TABLE `student_report` (
`id` int NOT NULL AUTO_INCREMENT,
`subject` varchar(45) NOT NULL,
`marks` varchar(45) NOT NULL,
`roll_num` int NOT NULL,
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
We have data in the ‘students’ and ‘student_report’ tables as follows.
students:
student_report:
As we can see from both tables we have a roll_num column common between two tables.
Now we will see what different types of joins are supported by MySQL.
We will see examples for each join using the ‘students’ and ‘student_report’ tables.
Example for INNER JOIN
- Here we will consider the ‘students’ table as table A and the ‘student_report’ table as table B.
- In both tables, we can see that roll number 1 and 2 is common between them.
- So when we apply INNER JOIN on both tables we will get the data common between both tables.
Query:
SELECT
*
FROM
students AS std
INNER JOIN student_report AS rpt
ON std.roll_num = rpt.roll_num;
Example for LEFT JOIN
- In the LEFT JOIN we will get all data from the ‘students’ table and matching data from ‘student_report’.
- We can see that roll numbers 1 and 2 is matching with the ‘student_report’ table so we got the data for it from the report table.
- Since roll numbers 3 and 6 are not present in the report table we will not get any data hence we can see null values.
Query:
SELECT
*
FROM
students AS std
LEFT JOIN student_report AS rpt
ON std.roll_num = rpt.roll_num;
Example for RIGHT JOIN
- For RIGHT JOIN we get all data from the ‘student_report’ table and matching data from the ‘students’ table.
- As we can see in the below output returned, We are also getting the data for roll numbers 4 and 5 from the report table and since these roll numbers are not present in the ‘students’ table we get null values.
Query:
SELECT
*
FROM
students AS std
RIGHT JOIN student_report AS rpt
ON std.roll_num = rpt.roll_num;
Example for CROSS JOIN
- The CROSS JOIN returns all matching records from both tables whether the other table matches or not.
- So, if there are roll numbers in ‘students’ which are not present in ‘student_report’, or if there are roll numbers in ‘student_report’ which are not in ‘students’ those data will also come irrespective of matching roll numbers from both tables.
Query:
SELECT
*
FROM
students AS std
CROSS JOIN student_report AS rpt;
- If we add where condition as ‘WHERE std.roll_num = rpt.roll_num’ in the above cross join we will get the same result as an inner join.
Query:
SELECT
*
FROM
students AS std
CROSS JOIN student_report AS rpt
WHERE std.roll_num = rpt.roll_num;
- We can also write a query for a cross join without actually adding the CROSS JOIN keyword and separating the two tables with a comma sign as below.
- This will give the same result as above. All the data from both tables will be returned irrespective of whether the common roll number is present or not.
- We can add where conditions like we added in the previous query to get the common data between them.
Query:
SELECT
*
FROM
students AS std
, student_report AS rpt;
