PostgreSQL Right Outer Join
What is Right Outer Join? How to Use Right Outer Join?
In the previous tutorials, we have talked about Inner Join and left outer join. Today I am going to explain the right outer join.
What is Right Outer Join?
Right outer is just the opposite of the left outer join. In the Right Outer join, all the data comes from the right table and which also matches a common column from the left table.
If some data on the right table doesn’t match with the left table then it will return a null value. To give you a better understanding of the right outer join, let me give me an example: First of all, understand the syntax of right outer join given below:
Right Outer Join Syntax:
1 2 3 4 | SELECT righttable1.column1, righttable2.column2… FROM righttable1 RIGHT JOIN righttable2 ON righttable1.common_field = righttable2.common_field; |
We are using the select query in the above syntax. We are selecting columns that we want to show in our results. So in the first line, we are selecting columns from table 1 & table 2.
After we joining both the tables with the “RIGHT JOIN” which is the keyword used for defining Right Outer Join. In the last line of the syntax, we are matching the common fields in both of the tables.
In the section below, I’m going to use the above syntax. So without wasting time let’s get started.
How to use right outer join?
We have already made the tables we will demo with and they are shown below.
TABLE A: BOOKCUSTOMERS
In this, we have listed customers with there address, salary, and age.
1 2 3 4 5 6 7 8 9 | +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | SAMUAL | 32 | Austin | 8000.00 | | 2 | Johnson | 25 | Ohio | 9500.00 | | 3 | Mathew | 23 | NEW York | 7000.00 | | 4 | Eva | 25 | Texas | 6500.00 | | 5 | April | 27 | San Marcos| 8500.00 | +----+----------+-----+-----------+----------+ |
TABLE B: ORDERS
In this, we have saved a list of orders given by customers.
1 2 3 4 5 6 7 8 | +-----+---------------------+-----------------+--------+ | OID | DATE | BOOKCUSTOMER_ID | AMOUNT | +-----+---------------------+-----------------+--------+ | 102 | 2019-12-08 00:00:00 | 3 | 4000 | | 100 | 2019-09-08 00:00:00 | 3 | 3500 | | 101 | 2019-08-20 00:00:00 | 2 | 6560 | | 103 | 2018-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-----------------+--------+ |
Let’s find out some data with the help of the right outer join.
SQL Query for Right Outer Join:
1 2 3 4 | SQL> SELECT ID, NAME, AMOUNT FROM BOOKCUSTOMERS RIGHT JOIN ORDERS ON BOOKCUSTOMERS.ID = ORDERS.BOOKCUSTOMERS_ID; |
I have previously explained the same above query with the syntax. In the above query, we are selecting columns(ID, NAME) from table 1 and another column (AMOUNT) from table 2.
After that we have added right outer join and also matches the ID columns from both the tables.
Query Result:
Here the result that you will get after running the above query.
1 2 3 4 5 6 7 8 | +----+----------+--------+ | ID | NAME | AMOUNT | +----+----------+--------+ | 3 | Mathew | 4000 | | 3 | Mathew | 3500 | | 2 | Johnson | 6560 | | 4 | Eva | 2060 | +----+----------+--------+ |
Conclusion
We hope this tutorial on Postgres Right Outer Joins has been helpful to you. Hopefully it cleared up the distinction Left Outer Joins and Right Outer Joins.
If you ever need advice or help with PostgresSQL, MongoDB, Elasticsearch, Redis, or CockroachDB please don’t hesitate to call us at Object Rocket.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started