RIGHT join SQL query for MySQL tables

Right Join will return common records with non matching records of second table.
RIGHT OUTER JOIN is also known as RIGHT JOIN.
Right Join requires two tables to be joined by linking with ON condition. We can add more condition by using WHERE clause.
Area as per venn diagram B + C

Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join

QUERY using RIGHT JOIN

The common records and the all not matched records of second table will be returned.
SQL DUMP of products , sales and customers table

Example : products & sales table

Area	Description
B & C	There are matching values in both tables for p_id column
A	These products ( table 1 ) have no matching p_id in sales ( table 2 )
D	These sales (table 2 ) have no matching p_id in products ( table 1 )

Type of join and expected output

Type	Table 1	Table 2	Details
LEFT	A + B	C	All from Left ( Table 1 ) and matching from right (Table 2 )
RIGHT	B	C + D	All from Right ( Table 2 ) and matching from left (Table 1 )
INNER	B	C	Matching from Left ( Table 1 ) and right (Table 2 )
CROSS	A + B	C + D	All from Left ( Table 1 ) join to all from right (Table 2 )

RIGHT JOIN will return rows where join column value is available in both tables and all balance rows of sales_v2 . Here the area B of products_v2 table and area C & D of sales_v2 table will be returned.

SELECT * from products_v2 a RIGHT  JOIN sales_v2 b on a.p_id=b.p_id

Output is here

p_id	product	price	sale_id	c_id	p_id	product	qty	store
2	RAM	90	4	4	2	RAM	2	DEF
2	RAM	90	7	2	2	RAM	3	ABC
2	RAM	90	8	3	2	RAM	2	DEF
3	Monitor	75	1	2	3	Monitor	2	ABC
3	Monitor	75	3	1	3	Monitor	3	ABC
3	Monitor	75	5	2	3	Monitor	3	ABC
3	Monitor	75	6	3	3	Monitor	2	DEF
3	Monitor	75	9	2	3	Monitor	2	ABC
4	CPU	55	2	2	4	CPU	1	DEF
			10	10	20	USB	2	ABC
			11	10	21	pendriv	1	DEF
			12	11	22	Cable	3	DEF

Using WHERE

We can add WHERE condition to RIGHT JOIN

SELECT * from products_v2 a RIGHT  JOIN sales_v2 b on a.p_id=b.p_id
 WHERE b.p_id=2

Output

p_id	product	price	sale_id	c_id	p_id	product	qty	store
2	RAM	90	4	4	2	RAM	2	DEF
2	RAM	90	7	2	2	RAM	3	ABC
2	RAM	90	8	3	2	RAM	2	DEF

SELECT * from products_v2 a RIGHT  JOIN sales_v2 b on a.p_id=b.p_id
  WHERE qty=1

p_id	product	price	sale_id	c_id	p_id	product	qty	store
4	CPU	55	2	2	4	CPU	1	DEF
			11	10	21	pendriv	1	DEF

Products which are sold but not available in products table

These are products for which there is no row available in product table (products_v2). ( Watch the last three rows of output of our main query at top ) We used IS NULL here.

SELECT * FROM products_v2 a 
RIGHT JOIN sales_v2 b ON a.p_id = b.p_id 
WHERE a.p_id is NULL

sale_id	c_id	p_id	product	qty	store
10	10	20	USB	2	ABC
11	10	21	pendriv	1	DEF
12	11	22	Cable	3	DEF

If we want to exclude the not matching rows from products_v2 table and show the matching rows only. We used NOT NULL here.

SELECT * FROM products_v2 a 
RIGHT JOIN sales_v2 b ON a.p_id = b.p_id 
WHERE a.p_id is NOT NULL

p_id	product	price	sale_id	c_id	p_id	product	qty	store
3	Monitor	75	1	2	3	Monitor	2	ABC
4	CPU	55	2	2	4	CPU	1	DEF
3	Monitor	75	3	1	3	Monitor	3	ABC
2	RAM	90	4	4	2	RAM	2	DEF
3	Monitor	75	5	2	3	Monitor	3	ABC
3	Monitor	75	6	3	3	Monitor	2	DEF
2	RAM	90	7	2	2	RAM	3	ABC
2	RAM	90	8	3	2	RAM	2	DEF
3	Monitor	75	9	2	3	Monitor	2	ABC

CROSS JOIN LEFT JOIN INNER JOIN : Table joining itself LINKING TABLES
Exercise : Sales - Agent using table JOIN and Date functions

Subscribe to our YouTube Channel here

SQL RIGHT OUTER JOIN query for MySQL

QUERY using RIGHT JOIN

Example : products & sales table

Using WHERE

Products which are sold but not available in products table

Subscribe