SQL RIGHT JOIN
The SQL RIGHT JOIN keyword returns all records from the right side table (tableB), with the matching records in the left side table (tableA).
The query result is NULL in the left side table when there is no match records.
Related Links
SQL RIGHT JOIN Syntax
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
RIGHT JOIN table_name2
ON table1.column_name1 = table2.column_name1;
or
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
RIGHT OUTER JOIN table_name2
ON table1.column_name1 = table2.column_name1;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
3 | SQL Server 2008 Transact-Sql | 200 | 2006 | Administration | Siva Kumar |
4 | Professional Microsoft SQL Server | 90 | 2015 | Database | Vidyavathi |
6 | Simply MySql | 190 | 2012 | Optimization | Nirmala |
7 | SQL Server Database Internals | 150 | 2006 | Security | Dharan |
8 | Mastering Oracle SQL | 136.33 | 2012 | Performance | Nirmala |
9 | Oracle Interview Questions | 65 | 2015 | Administration | Chandra |
Sample Database Table - BookOrder
BookID | OrderDate | Qty | DeliveryDate |
---|---|---|---|
1 | 12-02-2005 | 16 | 18-03-2005 |
2 | 18-02-1996 | 2 | 21-03-1996 |
4 | 19-08-2007 | 18 | 25-08-2007 |
6 | 14-01-2004 | 10 | 17-02-2004 |
8 | 14-08-2008 | 1 | 24-08-2008 |
12 | 18-06-2002 | 6 | 25-06-2002 |
Note:- There must be a common columns while selecting columns from multiple tables. In the "BookOrder" and "Books" both tables contains a common column called "ID".
SQL RIGHT JOIN - Example
Notice that the "BookID" column in the "Books" table refers to the "BookID" in the "BookOrder" table. The relationship between the two tables above is the "BookID" column.
Then, if we run the following SQL statement (that contains an RIGHT JOIN):
SELECT
BookOrder.BookID, BookOrder.BookName, Books.BookPrice, Books.RelYear, BookOrder.DeliveryDate
FROM Books
RIGHT JOIN BookOrder
ON Books.BookID = BookOrder.BookID;
The result of above query is:
BookID | BookName | BookPrice | RelYear | DeliveryDate |
---|---|---|---|---|
1 | Securing SQL Server | 18-03-2005 | ||
2 | PHP And MySQL Bible | 21-03-1996 | ||
4 | Professional Microsoft SQL Server | 90 | 2015 | 25-08-2007 |
6 | Simply MySql | 190 | 2012 | 17-02-2004 |
8 | Mastering Oracle SQL | 136.33 | 2012 | 24-08-2008 |
12 | MySql Interview Questions | 25-06-2002 |
Note: The SQL RIGHT JOIN keyword returns all the records from the right side table (Books), even if there are no matches in the left side table (BookOrder).
Related Links
SQL Right Join with Delete
On the basis of complicated WHERE clauses, we sometimes need to update or delete records.
Remove JOIN is an advanced structured query language (SQL) command that is used to delete rows from various tables using SQL JOIN, such that all rows from one table are deleted and the corresponding rows in another table are deleted, or based on the type of join operation performed in the query. DELETE and JOIN commands are essentially combined.
JOIN operations can be included in a DELETE statement.
JOIN operations can be zero, one, or several.
The DELETE command deletes records that meet the JOIN criteria.
SQL Syntax for delete JOIN:
DELETE t1
FROM table_name1 AS t1 JOIN {INNER, RIGHT,LEFT,FULL} table_name1 AS t2
ON t1.column_name = t2.column_name
WHERE condition;
When we wish to delete all data from one table (right) while keeping only the corresponding data from the other table, we need to use a SQL DELETE RIGHT join.
Example 1: Remove products that have not sold.
DELETE P
FROM Product P
RIGHT JOIN OrderItem I ON P.Id = I.ProductId
WHERE I.Id IS NULL;
Example 2: SQL Query to Illustrate DELETE RIGHT JOIN
When we need to eliminate anything in the activities and employee tables related to the sales and marketing department, Using a DELETE with RIGHT JOIN, we can now remove the department from the department table.
DELETE t2
FROM employees AS t1 RIGHT JOIN department AS t2
ON t1.departmentid = t2.departmentid
WHERE t2.departmentid NOT IN (SELECT t1.departmentid FROM employees as t1);
Note that we need to specify FROM twice, one for DELETE part and one for RIGHT JOIN part.
SQL Right Join and Left Join
LEFT JOIN and RIGHT JOIN are the two main types of joins in MySQL. The addition of non-matched rows is the key distinction between both joins. An INNER JOIN can be nested inside a LEFT JOIN or a RIGHT JOIN, but it cannot be nested within a LEFT JOIN or a RIGHT JOIN.
LEFT JOIN | RIGHT JOIN |
---|---|
It connects two or more tables and returns all entries from the first (left) of the two tables, even if the second (right) table has no matching values. | It joins two or more tables and returns all entries from the second (right) table, even if the first (left) table has no matching data. |
If there is no comparable row on the right side table, the result set will have a null value. | If no matching row on the left side table exists, the result set will have a null value. |
To make a left outer join, use the LEFT JOIN procedure. | To make a right outer join, use the RIGHT JOIN procedure. |
The result of the LEFT OUTER join is INNER JOIN + mismatched rows from the left table. | The INNER JOIN + mismatched rows from the right side table equals the outcome of the RIGHT OUTER JOIN. |
In SQL left outer join is written as *=. | The right outer join is written as =*. |
Syntax:
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1compopr table2.field2
The LEFT JOIN and RIGHT JOIN operations have these parts:
table1, table2 - The names of the tables from which records are combined.
field1, field2 - The names of the fields that have been linked together. The fields must be of the same data type and include the same type of information, but they do not have to be named the same.
compopr - Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."
Example 1: LEFT JOIN Example
Following SQL statement returns the matching records from both tables using the LEFT JOIN query:
SELECT cust_id, cust_name,
order_num, order_date
FROM customer LEFT JOIN orders
ON customer.cust_id = orders.order_id
WHERE order_date < '2020-04-30';
RIGHT JOIN Example
Following SQL statement returns the matching records from both tables using the RIGHT JOIN query:
SELECT cust_id, cust_name,
occupation, order_num, order_date
FROM customer
RIGHT JOIN orders ON cust_id = order_id
ORDER BY order_date;
Example 2: Left join
SELECT *
FROM
teams t
LEFT JOIN developers d
ON t.id = d.team_id ;
Right join
SELECT *
FROM
teams t
RIGHT JOIN developers d
ON t.id = d.team_id ;
SQL Right Join
In SQL, the RIGHT JOIN keyword returns all matched records (or rows) as well as records that are available in the right table but not in the left table. This implies that if the ON clause returns NULL in each field from the left table, the join will still return a row in the result.
If a row appears in the right table but not in the left, the output will include it, but with NULL values in each column from the left.
Syntax:
The syntax for a RIGHT JOIN is :-
SELECT column_name(s)
FROM tableA
RIGHT JOIN tableB ON tableA.column_name = tableB.column_name;
Example 1: SQL RIGHT JOIN EXAMPLE:
We'll look at two tables in this instance. The employee table provides information about the employees who work in the department, and the department table contains information about the department employee table :
Emp_no | Emp_name | Dept_no |
---|---|---|
E1 | Varun Singhal | D1 |
E2 | Amirtha Aggarwal | D2 |
E3 | Ravi Anand | D3 |
department table :
Dept_no | D_name | Location |
---|---|---|
D1 | IT | Delhi |
D2 | HR | Hyderabad |
D3 | Finance | Pune |
D4 | Testing | Noida |
D5 | Marketing | Mathura |
To perform right- join on these two tables we will use the following SQL query:
select emp_no , emp_name ,d_name, location
from employee
right join dept on employee.dept_no = department.dept_no;
Example 2: let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.
CUSTOMER TABLE:
ID | NAME | AGE | Salary |
---|---|---|---|
1 | ARYAN | 51 | 56000 |
2 | AROHI | 21 | 25000 |
3 | VINEET | 24 | 31000 |
4 | AJEET | 23 | 32000 |
5 | RAVI | 23 | 42000 |
and this is the second table:
ORDER TABLE:
DATE | )_ID | CUSTOMER_ID | AMOUNT |
---|---|---|---|
20-01-2012 | 001 | 2 | 3000 |
12-02-2012 | 002 | 2 | 2000 |
22-03-2012 | 003 | 3 | 4000 |
11-04-2012 | 004 | 4 | 5000 |
Here we will join these two tables with SQL RIGHT JOIN:
SELECT ID,NAME,AMOUNT,DATE
FROM CUSTOMER
RIGHT JOIN ORDER
ON CUSTOMER.ID = ORDER.CUSTOMER_ID;
Output:
ID | NAME | AMOUNT | DATE |
---|---|---|---|
2 | AROHI | 3000 | 20-01-2012 |
2 | AROHI | 2000 | 12-02-2012 |
3 | VINEET | 4000 | 22-03-2012 |
4 | AJEET | 5000 | 11-04-2012 |
Example 3: Consider the following two tables,
Table 1 − CUSTOMERS Table is as follows.
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 | MP | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Table 2 − ORDERS Table is as follows.
OID | DATE | CUSTOMER_ID | AMOUNT |
---|---|---|---|
102 | 2009-10-08 00:00:00 | 3 | 3000 |
100 | 2009-10-08 00:00:00 | 3 | 1500 |
101 | 2009-11-20 00:00:00 | 2 | 1560 |
103 | 2008-05-20 00:00:00 | 4 | 2060 |
Now, let us join these two tables using the RIGHT JOIN as follows.
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Output :
ID | NAME | AMOUNT | DATE |
---|---|---|---|
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 | Chaitali | 2060 | 2008-05-20 00:00:00 |
Example 4: Example of SQL Right join or right outer join:
Sample table: foods
ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
---|---|---|---|
1 | Chex Mix | Pcs | 16 |
6 | Cheez-It | Pcs | 15 |
2 | BN Biscuit | Pcs | 15 |
3 | Mighty Munch | Pcs | 17 |
4 | Pot Rice | Pcs | 15 |
5 | Jaffa Cakes | Pcs | 18 |
7 | Salt n Shake | Pcs |
Sample table: company
COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
---|---|---|
18 | Order All | Boston |
15 | Jack Hill Ltd | London |
16 | Akas Foods | Delhi |
17 | Foodies | London |
19 | sip-n-Bite | New York |
To get company ID, company name and company city columns from company table and company ID, item name columns from foods table, after an OUTER JOINING with these mentioned tables, the following SQL statement can be used:
SQL Code:
SELECT company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM company
RIGHT JOIN foods
ON company.company_id = foods.company_id;
SQL Right Join Both Sides
All rows from the right table are returned, as well as all corresponding rows from the left table.
Syntax:
The syntax of the SQL right outer join is as follows:
SELECT column1, column2...
FROM table_A
RIGHT JOIN table_B ON join_condition
WHERE row_condition
SQL right outer join is also known as SQL right join.
Example: The following example demonstrates the SQL right outer join:
SELECT c.customerid,
c.companyName,
orderid
FROM customers c
RIGHT JOIN orders o ON o.customerid = c.customerid
ORDER BY orderid;
The query returns all entries from the orders table as well as all rows from the customers table that match.
SQL Right Join Count
FULL JOIN will deliver the JOINed results as well as any non-matched entries from both tables.
Example 1: These will only come from the artists table in this dataset, and the result will be the same as our RIGHT JOIN:
SELECT count(*) FROM albums FULL JOIN artists ON albums.artist_id = artists.id;
Example 2: Count a specific field in the changes_cc table vs counting *:
SELECT u.id, realname, username, COUNT(c.id) as num
FROM users u
RIGHT JOIN changes_cc c
ON u.user_id = c.id
GROUP BY u.id
LEFT JOIN over a RIGHT JOIN, but they are both OUTER JOINs and work the same.
Example 3: sql rightjoin with count
select deptno, count(*)
from employees e
right outer join
departments d
using (deptno)
group by deptno;
Output:
DEPTNO | COUNT()* |
---|---|
30 | 3 |
20 | 3 |
40 | 2 |
10 | 3 |
drop table employees;
SQL Right Join with Distinct
If you choose distinct at the start, the result will be stripped of all computed rows. Only join if the values are distinct.
Example 1: I have 2 tables a and b where a.id is in b 0, 1 or many times.
All I'm wondering is if there is a more efficient way of saying
SELECT DISTINCT a.*, b.a_id
FROM a
RIGHT JOIN b ON b.a_id = a.id
given that a is really a complex sql statement on its own.
Example 2:
To connect and distribute knowledge in a single, well-structured, searchable location. I'd like to conduct a ValTbl join, but only for distinct values.
select distinct a.FirstName, a.LastName, v.District
from AddTbl a
right join ValTbl v
on a.LastName = v.LastName
order by a.FirstName;
SQL Right Join with Groupby
Example: use right join:
SELECT a.col,
SUM(b.col) as sumof
FROM
TableB b
RIGHT JOIN
TableA a
ON
b.col = a.col
GROUP BY
a.col;
The "table with solid data" must be titled after the words "RIGHT JOIN," while the "table with lacking data" must be named first. Because you write from left to right, you should always use LEFT join. Then attach the "tables with data holes" to the "tables that are solid," so the reliable data is on the left and the data with holes is joined to it.
SQL Right Join in Where
With a FULL JOIN, MySQL uses the When clause to return just those rows where the connecting tables have no corresponding data.
It also enables us to restrict the records returned using the Where Clause.
Example 1: The following query only returns companies with no corresponding food products in foods, as well as food products in foods that are not matched to the listed company.
SELECT a.company_id AS "a.ComID",
a.company_name AS "C_Name",
b.company_id AS "b.ComID",
b.item_name AS "I_Name"
FROM company a
FULL JOIN foods b
ON a.company_id = b.company_id
WHERE a.company_id IS NULL
OR b.company_id IS NULL
ORDER BY company_name;
Output:
a.ComID | C_Name | b.ComID | I_Name |
---|---|---|---|
19 | sip-n-Bite | Salt n Shake |
Example 2: In this example, we use WHERE Clause along with the SQL Right Outer Join.
SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
RIGHT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
WHERE Emp.[FirstName] IS NOT NULL;
Example 3: The following example illustrates this with the Right Join clause:
SELECT * FROM customers
RIGHT JOIN orders USING(customer_id)
WHERE price>2500 AND price<5000;
SQL Right Join Multiple Columns
Multiple columns from separate tables can be computed for the right join.
Tip: The left table in a join query is the one that occurs first in the JOIN clause, followed by the right table.
Example 1: The result contains all tables. There are no matching rows from the Customers table in the tables with numbers 1 and 5. As a result, the CustomerID and Name columns are both NULL.
You exchange the position of tables as shown here.
Select T.TableNo, C.CustomerID,C.Name
From Tables T
Right join Customers C
on (C.CustomerID=T.CustomerID)
Example 2: The Orders table is where the query and data are created. As a result, we've split the data into two tables. To query the data and retrieve the results, we'll use SQL RIGHT JOIN.
SELECT p.ProductID,
p.ProductName,
o.OrderDate
FROM Products p
RIGHT JOIN Orders o ON o.ProductID = p.ProductID
ORDER BY o.OrderID;
Example 3: By linking the employees and departments tables together using the common dept_id field, the following statement gets all accessible departments as well as the id, name, and hiring date of the employees that correspond to that department.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY dept_name;
SQL Right Join Null
The right outer join retrieves all matched records from both tables as well as all non-matching records from the right-hand table. Non-matching records will be assigned the value NULL in this situation. If we need to do a Right Outer Join, we'll need to filter out any entries with NULL values in the where condition.
Example 1: List customers that have not placed orders.
SELECT FirstName, LastName, City, Country, TotalAmount
FROM [Order] O
RIGHT JOIN Customer C ON O.CustomerId = C.Id
WHERE TotalAmount IS NULL;
This returns customers that, when joined, have no matching order.
Output:
FIRSTNAME | LASTNAME | CITY | COUNTRY | TOTALAMOUNT |
---|---|---|---|---|
Diego | Roel | Madrid | Spain | NULL |
Marie | Bertland | Paris | France | NULL |
Example 2: The following Right OuterJoin Query with the where clause does the same:
SELECT Cand.CandidateId,
Cand.FullName,
Cand.CompanyId,
Comp.CompanyId,
Comp.CompanyName
FROM Candidate Cand
Right JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
WHERE Cand.CompanyId IS NULL;
SQL Right Join Subquery
Example 1: To get the result set using a subquery and the RIGHT JOIN clause:
SELECT DISTINCT Customers.customer_id, Customers.first_name
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer_id
ORDER BY Customers.customer_id;
Example 2: Subquery in Right Join :
Select Cu.CustomerID,Cu.Name, Ord.Amount
From Customers Cu
Right join ( Select CustomerID, Sum(Amount) As Amount
From Orders
Group by CustomerID) Ord
On (Cu.CustomerID= Ord.CustomerID)
Example 3: You didn't select post_id in the subquery. You have to select it in the subquery like this:
SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM wp_woocommerce_order_items
RIGHT JOIN
(
SELECT meta_value As Prenom, post_id -- <----- this
FROM wp_postmeta
WHERE meta_key = '_shipping_first_name'
) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE wp_woocommerce_order_items.order_id =2198
SQL Right Join Three Tables
The select statement with the RIGHT JOIN keyword returns a new result table by joining three tables with all the records of the right table and corresponding values of the left tables.
Syntax :
You can join 3 tables using the following INNER JOIN syntax –
SELECT table1.column1_name, table1.column2_name,...,
table2.column1_name, table2.column2_name,...,
table3.column1_name, table3.column2_name,...,
FROM table1
RIGHT JOIN table2 ON table1.table1_id = table2.table1_id
RIGHT JOIN table3 ON table2.table2_id = table3.table2_id;
Example 1: Lets consider the below three table:
The PetTypes table:
PetTypeId | PetType |
---|---|
1 | Bird |
2 | Cat |
3 | Dog |
4 | Rabbit |
The Pets table:
PetId | PetTypeId | OwnerId | PetName | DOB |
---|---|---|---|---|
1 | 2 | 3 | Fluffy | 2020-11-20 |
2 | 3 | 3 | Fetch | 2019-08-16 |
3 | 2 | 2 | Scratch | 2018-10-01 |
4 | 3 | 3 | Wag | 2020-03-15 |
5 | 1 | 1 | Tweet | 2020-11-28 |
6 | 3 | 4 | Fluffy | 2020-09-17 |
7 | 3 | 2 | Bark | NULL |
8 | 2 | 4 | Meow | NULL |
The Owners table:
OwnerId | FirstName | LastName | Phone | |
---|---|---|---|---|
1 | Homer | Connery | (308) 555-0100 | homer@example.com |
2 | Bart | Pitt | (231) 465-3497 | bart@example.com |
3 | Nancy | Simpson | (489) 591-0408 | NULL |
4 | Boris | Trump | (349) 611-8908 | NULL |
5 | Woody | Eastwood | (308) 555-0112 | woody@example.com |
Here’s an example of performing a right join on all three tables.
SELECT p.PetName,
pt.PetType,
CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Pets p RIGHT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
RIGHT JOIN Owners o
ON p.OwnerId = o.OwnerId;
Output:
PetName | PetType | PetOwner |
---|---|---|
Tweet | Bird | Homer Connery |
Scratch | Cat | Bart Pitt |
Bark | Dog | Bart Pitt |
Fluffy | Cat | Nancy Simpson |
Fetch | Dog | Nancy Simpson |
Wag | Dog | Nancy Simpson |
Fluffy | Dog | Boris Trump |
Meow | Cat | Boris Trump |
NULL | NULL | Woody Eastwood |
Example 2: Imagine the following scenario: We've built two tables, "customers" and "orders." Create a new table called "contacts" that has the following information:
Execute the following statement to join the three table customers, orders, and contacts:
SELECT customers.customer_id,
cust_name, order_id, price, cellphone
FROM customers
RIGHT JOIN contacts ON customer_id = contact_id
RIGHT JOIN orders ON customers.customer_id = orders.customer_id ORDER BY order_id;
Example 3:
SELECT students.full_name, students.gender,
contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
RIGHT JOIN students ON universities.university_id = students.university_id
RIGHT JOIN contacts ON students.student_id = contacts.student_id;
Output :
full_name | gender | mobile_number | email_address | university_name |
---|---|---|---|---|
Noor Kha | Male | 2222222222 | aaaa222@gmail.com | SRM University |
Avneesh Mishra | Male | 5555555555 | bbbb555@gmail.com | Amity University |
Monika Singh | Female | 7777777777 | cccc777@gmail.com | Anna University |
Aaliya Khan | Female | 8888888888 | dddd888@gmail.com | Hindustan University |
Aaliya Khan | Female | 1010101010 | eeee101@gmail.com | Hindustan University |
Aaliya Khan | Female | 1212121212 | ffff121@gmail.com | Hindustan University |
Avneesh Mishra | Male | 1313131313 | gggg131@gmail.com | Amity University |
Avneesh Mishra | Male | 1414141414 | iiii141@gmail.com | Amity University |
NULL | NULL | 1515151515 | jjjj151@gmail.com | NULL |
Example 4: apply the query. Which is given below:
Select stu.s_id,
course.coursename, marks.marks, stu.s_name
from stu Right join marks
On stu.s_id= marks.s_id
Right join course
on marks.course_id = course.course_id ;
SQL Right Join vs Right Outer Join
The PostgreSQL RIGHT JOIN joins two tables and retrieves rows depending on a criteria that is met in both tables, with unmatched rows obtainable from the table created following the JOIN clause.
There is no distinction between a RIGHT JOIN and a RIGHT OUTER JOIN in SQL Server. They generate the same result and behave similarly.
So, in case of RIGHT JOIN or RIGHT OUTER JOIN, PostgreSQL -
1. selects all values from the right table.
2. merges them with the column names from the left table (as given in the criteria).
3. Retrieve the matched rows from both related tables.
4. assigns the value of every column in the left table that does not match the right table to NULL.
Syntax:
Select *
FROM table1
RIGHT [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;
Example 1:
SELECT invoice.invoice_no,invoice.sold_qty,
item.item_no,item_descrip
FROM invoice
RIGHT JOIN item
ON item.item_no=invoice.item_no;
OR
SELECT invoice.invoice_no,invoice.sold_qty,
item.item_no,item_descrip
FROM invoice
RIGHT OUTER JOIN item
ON item.item_no=invoice.item_no;
Example 2: Let us demonstrate that there is no difference between RIGHT JOIN and RIGHT OUTER JOIN using instances. Generate a demo database with two tables, Customers and Orders, and sample data by running the script below :
RIGHT JOIN
SELECT *
FROM Customers C
RIGHT JOIN Orders O
ON O.CustomerId = C.CustomerId
RIGHT OUTER JOIN
SELECT *
FROM Customers C
RIGHT OUTER JOIN Orders O
ON O.CustomerId = C.CustomerId
SQL Right Join Without Duplicates
To make rows with multiple sets of data identical, sort column values in a certain order inside rows. Then you can delete duplicates with SELECT DISTINCT.
Example 1: I'm using Access and am attempting to query all the data from the RIGHT TABLE to see if they have connected records in the LEFT TABLE. The RIGHT JOIN I'm using is showing duplicates of the records in A (for example, if a record in A has 5 related/linked records in B, record A will appear 5 times). I only want to show the records from A once.
Current query:
SELECT A.ID, A.variable1,
A.variable2, B.ID, B.variable1, B.variable2
FROM A RIGHT JOIN B ON A.ID = B.ID
ORDER BY A.variable1;
Example 2: Right join to remove duplicate records:
select a.*, meansale from
(select distinct * from table2)a
right join
(select distinct GVKEY, year, meansale from table 2)b
on a.gvkey=b.gvkey
and a.lag_year=b.year;
SQL Right Join on Multiple Condition
Example 1: fetch posts with multiple filters, my database is as below,
"Meta" Table
id | meta_key | meta_value | object_name | object_id |
---|---|---|---|---|
2 | location | new city | post | 2 |
1 | post_type | section post | post | 2 |
"Posts" Table
id | title | except | description | mime_type | status | parent | post_class | dtime | sort_id | author_id | time |
---|---|---|---|---|---|---|---|---|---|---|---|
2 | testing | -1 | post | 2021-04-12 | 0 | 0 | 03:06:06 | ||||
1 | test post | someDescription | active | -1 | post | 2021-04-12 | 0 | 1 | 12:09:03 |
SELECT p.id,90p.title
FROM posts p
RIGHT JOIN meta m
ON p.id = m.object_id and m.object_name='post'
WHERE m.meta_key='location' and m.meta_value='new city'
and m.meta_key='post_type' and m.meta_value='section'
group by p.id
and I tried the above SQL but trouble is I am unable to form right conditional statement, i need to get all the post which have meta key 'post type' with value 'section' and meta key 'location' with value 'new city'.
Example 2: Just move the extra condition into the RIGHT JOIN ON multiple criteria, this way the existence of b is not required to return a result:
SELECT a.* FROM a
RIGHT JOIN b ON a.group_id=b.group_id AND b.user_id!=$_SESSION{['user_id']}
WHERE a.keyword LIKE '%".$keyword."%'
GROUP BY group_id;
Example 3: Right Join Multiple Conditions
Select Cu.CustomerID,Cu.Name, Ord.OrderDate, Ord.Amount
From Orders Ord
Right join Customers Cu
On (Cu.CustomerID= Ord.CustomerID and OrderDate='2019-12-10')
SQL Right Outer Join
When joining tables, the Right Outer Join technique is used. It is used in SQL Server to extract all matching records from both tables engaged in the join, as well as all non-matching records from the right-hand table. In that situation, the null value will be assigned to the unmatched data.
Unmatched rows from only the table specified following the RIGHT OUTER JOIN clause are included in the output.
The MySQL Right Outer Join is a Join Type that returns all existing records (or rows) from the Right table, as well as matching rows from the Left table. The NULL values were supplied for all the unmatched rows from the left table.
The Right Outer Join in MySQL is also known as Right Join. As a result, using the Outer Keyword is optional.
Syntax:
The basic syntax of MySQL Right outer Join is as shown below:
SELECT Table1. Column(s), Table2. Column(s)
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.Common_Column = Table2.Common_Column
Example 1: The query that follows is an instance of a Right Outer Join that joins the Company and Candidate tables, yielding the results displayed in the figure above.
SELECT Cand.CandidateId,
Cand.FullName,
Cand.CompanyId,
Comp.CompanyId,
Comp.CompanyName
FROM Candidate Cand
RIGHT OUTER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
If you look at the diagram above, we have 6 rows. We got all of the matching rows from both tables, as well as non-matching data from the Company Table on the right side. You can use the right join keyword rather than the right outer join keyword. As a right outer join, this will likewise operate as intended.
Example 2: The tables from the sample data are used for joins in the given description. Perform this query to include rows from the PRODUCTS table that have no equivalent rows in the PARTS table:
SELECT PART, SUPPLIER, PRODUCTS.PROD#, PRODUCT, PRICE
FROM PARTS RIGHT OUTER JOIN PRODUCTS
ON PARTS.PROD# = PRODUCTS.PROD#
AND PRODUCTS.PRICE>10.00;
Output:
PART | SUPPLIER | PROD# | PRODUCT | PRICE |
---|---|---|---|---|
WIRE | ACWF | 10 | GENERATOR | 45.75 |
MAGNETS | BATEMAN | 10 | GENERATOR | 45.75 |
BLADES | ACE_STEEL | 205 | SAW | 18.90 |
------- | -------- | 30 | RELAY | 7.55 |
-------- | ------- | 505 | SCREWDRIVER | 3.70 |
Only if the product number of a row in the PARTS table matches the product number of a row in the PRODUCTS table and the price for that row is greater than ten dollars is that row included in the result table.
Because the PRODUCTS table can include rows in the result table with nonmatching product numbers and the PRICE column is in the PRODUCTS table, rows with PRICE less than or equal to 10.00 are included in the result. These rows in the result table have null values in the PARTS columns.
Example 3: The right outer join query below displays all of the columns in the Department table, as well as matching records from the Employ table.
USE company;
SELECT * FROM employ
RIGHT OUTER JOIN department
ON employ.DeptID = department.DeptID;
MySQL's right outer join is presenting 12 records from the department table and the Employ table in the screenshot beneath. However, the employ table's last two rows return NULL values. The reason for this is that the DeptID for those records in the Employ database is NULLS.