MySQL RIGHT 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 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.

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.

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.

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.

Table- indian_book_authors

The relationship between all the table are shown below.

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:

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:

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

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:

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:

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.