MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

 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 −
ID
NAME
AGE
ADDRESS
SALARY
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
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 −
OID
DATE
CUSTOMER_ID
AMOUNT
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
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 −
ID
NAME
AMOUNT
DATE
3
Kaushik
3000.00
2009-10-08 00:00:00
3
Kaushik
1500.00
2009-10-08 00:00:00
2
Khilan
1560.00
2009-11-20 00:00:00
4
Chaitali
2060.00
2008-05-20 00:00:00
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 −
EID
EMPLOYEE_NAME
SALES_MADE
102
SARIKA
4500
100
ALEKHYA
3623
101
REVATHI
1291
103
VIVEK
3426
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 −
ID
NAME
DATE
EMPLOYEE_NAME
3
Kaushik
2009-10-08 00:00:00
SARIKA
3
Kaushik
2009-10-08 00:00:00
ALEKHYA
2
Khilan
2009-11-20 00:00:00
REVATHI
4
Chaitali
2008-05-20 00:00:00
VIVEK
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 −
ID
NAME
DATE
Amount
3
Kaushik
2009-10-08 00:00:00
3000.00
3
Kaushik
2009-10-08 00:00:00
1500.00
2
Khilan
2009-11-20 00:00:00
1560.00
4
Chaitali
2008-05-20 00:00:00
2060.00
Right Join Using a Client Program
We can also perform the Right join operation on one or more tables using a client program.
Syntax
PHPNodeJSJavaPython
To join two tables using right join through a PHP program, we need to execute the SQL query with RIGHT JOIN clause using the mysqli function query() as follows −
$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);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$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();
﻿
Output
The output obtained is as follows 
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    
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
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

OID	DATE	CUSTOMER_ID	AMOUNT
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

ID	NAME	DATE	EMPLOYEE_NAME
3	Kaushik	2009-10-08 00:00:00	SARIKA
3	Kaushik	2009-10-08 00:00:00	ALEKHYA
2	Khilan	2009-11-20 00:00:00	REVATHI
4	Chaitali	2008-05-20 00:00:00	VIVEK

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Right Join

MySQL Right Join

Syntax

Example

Output

Joining Multiple Tables with Right Join

Syntax

Example

Output

Right Join with WHERE Clause

Syntax

Example

Output

Right Join Using a Client Program

Syntax

Example

Output