Open In App

MySQL RIGHT JOIN

Last Updated : 28 Dec, 2023
Improve
Improve
Like Article
Like
Save
Share
Report

In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, joins enable the merging of multiple tables based on the common columns. In this article, we are going to explore MySQL RIGHT JOINS which is a type of outer join in MySQL.

Introduction to MySQL RIGHT JOIN

Before exploring RIGHT JOIN make sure that your concepts about the INNER JOIN are clear. MySQL RIGHT JOIN is a type of outer join that returns a resulting table that contains all the records from the right table and the matched records from the left table. In simple words, the data of the 2nd table is returned along with the data that is common between the first and the second table. If there is no matched data found then only the data of the right table is returned. Consider an example where we have two tables orders and customers. A Right JOIN will allow you to combine these tables ensuring that all the customer’s data appears in the final result of the query even if they haven’t placed any orders yet. The right table is the main priority of the right join along with the matched data between the two tables.

MYSQL_RIGHT_JOIN_GFG

Syntax:

Following is the syntax for Right join in MYSQL:

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Explanation: In the above Query, we have defined the syntac to perform RIGHT JOIN in MYSQL. Here we use ON Clause to specify the conditions of matching column from both table called table1 and table2.

Application of RIGHT JOIN in MySQL

  • Retrieves all record from RIGHT table: The primary purpose of a RIGHT JOIN is to retrieve all records from the RIGHT (or first) table, regardless of whether there are matching records in the right (or second) table. This ensures that no data is RIGHT out in the result set.For example if we have two tables employees and projects and we perform a RIGHT join between them then we can find out which are the employees who are not assigned to a project till now.
  • Aggregate Data and Fetch Results :Using the GROUP BY clause along with the RIGHT JOIN we can combine data from multiple tables and then fetch the required results. Like we can calculate the count, sum , average of the grouped data formed through the RIGHT join.
  • Perfom data cleanup: RIGHT join can be used to find out the unmatched records between two tables. Using this we can find the unwanted result set in our database and delete them accordingly. For example if we have two tables orders and customers and we want to delete the records of the customer who has not placed any order then we can easily find out the list of such customers by perfroming RIGHT join between the customers and order table and delete them easily.

Examples on MySQL RIGHT JOIN

To understand RIGHT JOIN in more depth manner we need some table on which we will perform operations or queries. we have created three tables called indian_books, indian_authors and indian_book_authors are as follow.

if you don’t know How to Create a Tables in MYSQLthen refer this.

Here indian_books consists book_id, title, publication_year as Columns and here book_id assign as PRIMARY KEY. After inserting some data into the indian_books table. Our table looks.

DummyTable1_Right_Join

Table-Indian_books

Here indian_authors consists author_id, author_name as Columns and here author_id assign as PRIMARY KEY. After inserting some data into the indian_books table. Our table looks.

DummyTable2_Right_Join

Table-indian_authors

Here indian_book_authors consists book_id, author_id as Columns. Here book_id, author_id considered as FOREIGN KEY which is REFERENCES to indian_books(book_id), and indian_authors(author_id). After inserting some data into the indian_books table. Our table looks.

DummyTable3_Right_Join

Table- indian_book_authors

The relationship between all the table are shown below.

Relationship_Between_dummy_tables

Relationship between the tables

1. Simple MySQL RIGHT JOIN with Using Clause

SELECT *
FROM indian_books
RIGHT JOIN indian_book_authors USING (book_id);

Output:

RightJoin_UsingClause

Output

Explanation: The following query gets all the records from the right table indian_book_authors and the matching records from the left table indian_books.The query uses the USING clause which perfroms the right join operation based on a column which is common to both the tables and has matching records. After the join is performed we get the name of all the book authors from the right table along with the book that are written by the specific authors.

2. MySQL RIGHT JOIN with GROUP BY Clause

SELECT indian_authors.author_name, COUNT(indian_books.book_id) as book_count
FROM indian_authors
RIGHT JOIN indian_book_authors ON indian_authors.author_id = indian_book_authors.author_id
RIGHT JOIN indian_books ON indian_book_authors.book_id = indian_books.book_id
GROUP BY indian_authors.author_name;

Output:

RightJoin_GroupBy_Clause

Output

Explanation: In the above query, Group by is used in MYSQL to group the rows that has similar values .It returns the frequency of books written by each author. It uses RIGHT JOIN to join indian_authors table and the indian_book_authors table and then aggregates the records using the GROUP BY clause to calculalte the count of books written by each author .The aggregate function count is used to calculate the frequency of authors once it is grouped.

3. MySQL RIGHT JOIN with WHERE Clause

SELECT indian_authors.author_name, indian_books.title
FROM indian_authors
RIGHT JOIN indian_book_authors ON indian_authors.author_id = indian_book_authors.author_id
RIGHT JOIN indian_books ON indian_book_authors.book_id = indian_books.book_id
WHERE indian_books.publication_year < 2015

RightJoin_Where_Clause

Output

Explanation: In the above query, WHERE clauses is used in MYSQL to filter the result set based on a specific condition. This query perfroms a right join between the tables indian_authors and indian_book_authors to return the name of the author and the book he/she has written but only those authors who have published a book before 2015. To filter the result according to this condition we use the WHERE clause or else we would have got the names of all the authors and the books written by them.

4. MySQL RIGHT JOIN on Multiple Tables

SELECT *
FROM indian_authors
RIGHT JOIN indian_book_authors ON indian_authors.author_id = indian_book_authors.author_id
RIGHT JOIN indian_books ON indian_book_authors.book_id = indian_books.book_id;

Output:

RightJoin_multiple_tables

Output of the above query

Explanation: This query performs a join on more than two tables. Yes it is possible to perform join on more than two tables, you might encounter situations where you will have to join more than two tables to fetch the required data. So the above query performs a right join on all the the three tables and returns all the records of the indian_authors table along with the matching records of other two indian_books and indian_book_authors table.

5. Use of MySQL RIGHT JOIN to Get Unmatched Records

SELECT indian_books.title
FROM indian_books
RIGHT JOIN indian_book_authors ON indian_books.book_id = indian_book_authors.book_id
WHERE indian_book_authors.author_id IS NULL;

Output:

RightJoin_Unmatched_Records

Output -NULL no matched records found

Explanation: The above query is used to get the unmatched records within the tables. For example, consider an example where you have two tables customers and orders and you want to find out customers who have never placed an order. In such situations we can use this query to get the unmatched results. It uses a RIGHT JOIN and a WHERE clause to filter out authors with unmatched entries in indian_book_authors. We can see that in our case we don’t have any resultant rows after executing this query which means that in our database every author has published atleast one book that is why the result set is NULL.

Conclusion

In this article, We have learned about RIGHT JOIN in MYSQL and explored its various concepts using a simple database of 3 tables. You can perform right join operation whenever the right table is most priority and you want to fetch the records of the right table along with the matching records of the left table. However always ensure to use the right syntax inorder to get the exact results you want from the query.



Previous Article
Next Article

Similar Reads

Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
4 min read
MySQL DELETE JOIN
MySQL is an open-source, user-friendly, powerful, and popular choice, relational database management system. When maintaining and modifying data, tables usually interact in a complex way. MySQL's DELETE JOIN function is one of its most powerful functions. MySQL DELETE JOIN is explored in detail in this article, which also offers examples to help vi
3 min read
MySQL UPDATE JOIN
A widely used open-source relational database management system that allows you to efficiently store, organize, and retrieve data. Developed by Oracle, My SQL is widely used for building and managing databases that handle interactive websites and applications. We'll discuss the syntax, and demonstrate how this dynamic duo can efficiently modify dat
6 min read
MySQL CROSS JOIN
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL also provides the CROSS JOIN statement to combine
3 min read
MySQL Inner Join
Have you encountered a query requiring you to somehow join or get the data spread across two or more tables? Are you wondering how to achieve the same by writing a MySQL query? Do you want to learn about MySQL INNER JOIN? If your answer to any of these questions is "YES", you have arrived at the correct place. Follow along with the below article an
7 min read
MySQL SELF JOIN
Joins are very important for effective data retrieval and analysis. The 'JOIN' clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like inner join, outer join, left join, right join, full join, and self join. In this article, we will discuss the concept of MySQL SELF J
4 min read
INNER JOIN ON vs WHERE clause in MySQL
Have you ever wondered about the optimal use of INNER JOIN ON versus the WHERE clause in MySQL queries? In this article, we'll delve into the distinctions between these two approaches, elucidating their respective purposes, syntax, and best practices. By the end, you'll have a solid grasp of how to leverage these tools to enhance the efficiency and
4 min read
MySQL LEFT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, Joins enable the merging of multiple tables based on the common columns.  In this article, we are going to explore MySQL LEFT JOIN Keyword which is a type of Outer Join in MySQL. We will cover every
7 min read
How to Use Full Outer Join in MySQL
The FULL OUTER JOIN or FULL JOIN is a useful technique in MySQL that allows us to fetch all rows from both tables when there is a match in the records. In this article, we will learn about the FULL OUTER JOIN through practical examples and MySQL queries. MySQL FULL OUTER JOINThe FULL OUTER JOIN in MySQL returns all rows of both tables involved in t
4 min read
When should we use CROSS APPLY over INNER JOIN in MySQL
CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data. It becomes very useful when we need to perform row-
4 min read