SQL RIGHT OUTER JOIN query for MySQL

Right Join Venn diagram 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

  • Video Tutorial on all SQL JOINS including RIGHT 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

Right Join of two tables
AreaDescription
B & C There are matching values in both tables for p_id column
AThese products ( table 1 ) have no matching p_id in sales ( table 2 )
DThese sales (table 2 ) have no matching p_id in products ( table 1 )
Type of join and expected output
TypeTable 1Table 2Details
LEFTA + B C All from Left ( Table 1 ) and matching from right (Table 2 )
RIGHTB C + D All from Right ( Table 2 ) and matching from left (Table 1 )
INNERB C Matching from Left ( Table 1 ) and right (Table 2 )
CROSSA + 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_idproductpricesale_idc_idp_idproductqtystore
2RAM90442RAM2DEF
2RAM90722RAM3ABC
2RAM90832RAM2DEF
3Monitor75123Monitor2ABC
3Monitor75313Monitor3ABC
3Monitor75523Monitor3ABC
3Monitor75633Monitor2DEF
3Monitor75923Monitor2ABC
4CPU55224CPU1DEF
101020USB2ABC
111021pendriv1DEF
121122Cable3DEF

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_idproductpricesale_idc_idp_idproductqtystore
2RAM90442RAM2DEF
2RAM90722RAM3ABC
2RAM90832RAM2DEF
SELECT * from products_v2 a RIGHT  JOIN sales_v2 b on a.p_id=b.p_id
  WHERE qty=1
p_idproductpricesale_idc_idp_idproductqtystore
4CPU55224CPU1DEF
111021pendriv1DEF

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
p_idproductpricesale_idc_idp_idproductqtystore
101020USB2ABC
111021pendriv1DEF
121122Cable3DEF
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_idproductpricesale_idc_idp_idproductqtystore
3Monitor75123Monitor2ABC
4CPU55224CPU1DEF
3Monitor75313Monitor3ABC
2RAM90442RAM2DEF
3Monitor75523Monitor3ABC
3Monitor75633Monitor2DEF
2RAM90722RAM3ABC
2RAM90832RAM2DEF
3Monitor75923Monitor2ABC
CROSS JOIN LEFT JOIN INNER JOIN : Table joining itself


plus2net.com






Post your comments , suggestion , error , requirements etc here
















We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer