MySQL Right JOIN

Right JOIN in MySQL is for the same purpose of getting columns from more than one table as like as MySQL Left JOIN we have seen some days before. But, right JOIN differs with the starting point with which table data is preserved to compare other table. It preserves right table data, instead of considering left table as like as MySQL Left JOIN.

mysql_right_join

Now, we are going to look into how this Right JOIN is performed with the same tables which we took for explaining Left JOIN, to catch the point of difference between these two type of JOIN operations. So, the left JOIN query has been changed by replacing MySQL LEFT keyword as RIGHT as shown below.

$query = "SELECT students.student_name, students.student_email, users.user_type FROM students RIGHT JOIN users ON (students.student_id = users.user_id)";

By executing this query, then it keeps the right table rows and based on which it searches for the match if any that satisfies the condition specified using ON clause of the above query.

Before starting, let us look into the tables once again for reference as follows.

students Table

mysql_student_table

users Table

mysql_user_table

Now, we are going to join these two tables as students RIGHT JOIN users by applying same condition with ON clause. As we discuss already, RIGHT JOIN consider right table first which is users here in this query. So, it picks user_type for all users based on user_id. And then, it will check for the match if any as specified in ON conditions. That is, if the user_id and student_id of student table is same, then the corresponding value of the columns student_name and student_email will be returned as query result. Otherwise, it will return NULL value for left table columns.

On seeing the above tables, all the user_id of users table got matched with the student_id field of other table, except where the user_id is 6 for which the student_name and student_email has no values as shown in the output screen shot below.

mysql_right_join_output

And, the following PHP program is used to perform right join operations as we discussed above that has slight query keyword modification on the MySQL left JOIN program.

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
$query = "SELECT students.student_name, students.student_email, users.user_type FROM students RIGHT JOIN users ON (users.user_id = students.student_id)"; 
$result = mysql_query($query) or die(mysql_error());
?>
<table width="500px" cellpadding="5" cellspacing="1" border="1" style="border-color:#CCCCCC">
<tr>
<td width="33%"><strong>Student Name</strong></td>
<td width="33%"><strong>Student Email</strong></td>
<td width="33%"><strong>User Type</strong></td>
</tr>
<?php
while($row = mysql_fetch_array($result)){
?>
<tr>
<td width="33%"><?php echo $row['student_name']; ?></td>
<td width="33%"><?php echo $row['student_email']; ?></td>
<td width="33%"><?php echo $row['user_type']; ?></td>
</tr>
<?php
}
?>

This MySQL code tutorial was published on June 8, 2013.

↑ Back to Top