MySQL - Right Join

MySQL Right Join

The Right Join or Right Outer Join query in MySQL returns all rows from the right table, even if there are no matches in the left table. So, if zero records are matched in the left table, the right join will still return a row in the result, but with a NULL value in each column of the left table.

In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

The resultant table displayed after implementing the Right Join is not stored anywhere in the database.

Syntax

Following is the basic syntax of Right Join in SQL −

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

Assume we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as −

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

Right join Query

Now, let us join these two tables using the Right Join query as follows.

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

This would produce the following result −

Joining Multiple Tables with Right Join

Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first.

Syntax

Following is the syntax to join multiple tables using Right Join −

SELECT column1, column2, column3...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name
RIGHT JOIN table3
ON table2.column_name = table3.column_name
.
.
.

Example

Here, let us consider the previously created tables CUSTOMERS and ORDERS; along with the newly created table EMPLOYEE.

We will create the EMPLOYEE table using the query below −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);

The table is created as −

Let us join these three tables using the Right Join query given below −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
RIGHT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Through this query, we are trying to display the records of Customer IDs, Customer names, Orders made on specific dates and names of the employees that sold them.

Output

The resultant table is obtained as follows −

Right Join with WHERE Clause

A WHERE Clause is used to filter out records that satisfy the condition specified by it. This clause can be used with the Right Join technique to apply constraints on the result-set obtained.

Syntax

The syntax of Right Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the following query −

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 1000.00;

Output

The resultant table is obtained as follows −

Right Join Using a Client Program

We can also perform the Right join operation on one or more tables using a client program.

Syntax

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author';
$mysqli->query($sql);

sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author";
con.query(sql);  

String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

right_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(right_join_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
        FROM tutorials_tbl a RIGHT JOIN tcount_tbl b
        ON a.tutorial_author = b.tutorial_author';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the both table details after executing right join! \n";
    while ($row = $result->fetch_assoc()) {
        printf( "Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] );
        printf("\n");
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

following is the both table details after executing right join!
Id: , Author: , Count: 20
Id: , Author: , Count: 5
Id: , Author: , Count: 4
Id: , Author: , Count: 20
Id: , Author: , Count: 1
Id: 3, Author: Sanjay, Count: 1    

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //Right Join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});     

[
   { tutorial_id: null, tutorial_author: null, tutorial_count: 20 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 5 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 4 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 20 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 1 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 1 },
   { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class RightJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL RIGHT JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after LEFT Join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}   

Connected successfully...!
Table records after LEFT Join...!
null null 20
null null 5
null null 4
null null 20
1 John Paul 1
3 Sanjay 1

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
right_join_query = f"""
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUST_ID
"""
cursorObj.execute(right_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()         

(3, 'kaushik', 3000, '2009-10-08 00:00:00')
(3, 'kaushik', 1500, '2009-10-08 00:00:00')
(2, 'Khilan', 1560, '2009-11-20 00:00:00')
(4, 'Chaital', 2060, '2008-05-20 00:00:00')