PostgreSQL Join for ObjectRocket Instance
Introduction
In this tutorial we’ll be covering how to do Joins using a PostgreSQL database hosted on the ObjectRocket platform. There are endless ways to use Joins to gather data from different tables and we’ll be showing some basic examples of that. We’ll cover JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in this article with examples of each. Now let’s dive in and cover how to do a PostgreSQL Join on an ObjectRocket instance.
Prerequisites
Before you proceed with the examples for the PostgreSQL JOIN
clause for your ObjectRocket instance, you will need to have the following:
ObjectRocket Instance of PostgreSQL
Set up a PostgreSQL instance on your ObjectRocket account in the create instance tab.
Connection of PostgreSQL to the database
You can use the command prompt and use the psql
command or the SQL Shell (psql), if you are using a Windows operating system.
The following is an example of using the SQL Shell (psql) to connect to the ObjectRocket instance of PostgreSQL:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [postgres]: Port [5432]: 4144 Username [postgres]: orkb Password FOR USER orkb: |
You can use the psql
command on the terminal window to connect to the ObjectRocket instance, if you are using a UNIX-based operating system.
The following is an example of using the UNIX terminal window to connect to the ObjectRocket instance of PostgreSQL:
1 2 3 4 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \ -U orkb \ -d postgres \ -p 4144 |
NOTE: Make sure to change the port and ingress URI from the above example so that it matches your instance. Check the CONNECT tab for your instance in Mission Control to get your Postrges instance’s URL and port information.
PostgreSQL join clause
The PostgreSQL join is used to combine the columns of multiple tables into one.
Joins supported by PostgreSQL
Join | Description |
---|---|
Inner Join | return all rows that matches on the tables |
Left Join | return all the records from the left table and the records from the right table that matches the left table |
Right Join | return all the records from the right table and the records from the right left that matches the right table |
Full Outer Join | return all rows from both tables |
Cross Join | return the rows from the tables like Cartesian Product |
Natural Join | return the rows that has the same values on each column |
Self-Join | joined the table on itself |
PostgreSQL create table example
Let us first create two tables that we will use on our examples.
The following will create a tables named 'varsity_players'
and 'top_students'
:
1 2 3 4 5 6 7 8 9 | CREATE TABLE varsity_players ( id INT PRIMARY KEY, name VARCHAR ); CREATE TABLE top_students ( id INT PRIMARY KEY, name VARCHAR ); |
Postgres update & join examples
Now let us insert some records into our tables so we have some data to work with.
The following commands will insert several records into our tables 'varsity_players'
and 'top_students'
:
1 2 3 4 5 6 7 8 9 10 11 12 13 | INSERT INTO varsity_players VALUES ('1', 'Diana Hoffman'), ('2', 'Gregory Colon'), ('3', 'Caroline Carter'), ('4', 'Peter Vazquez'), ('5', 'Geoffrey Cortes'); INSERT INTO top_students VALUES ('1', 'Diana Hoffman'), ('2', 'Ernest Blake'), ('3', 'Caroline Carter'), ('4', 'Gregory Colon'), ('5', 'Cathy England'); |
Now that we have tables with some data let’s continue with our examples.
PostgreSQL inner join examples
For this example, we will list the varsity players that are also top students by using the INNER JOIN
, see the following:
1 2 3 4 | SELECT vp.id, vp.name, ts.id, ts.name FROM varsity_players AS vp INNER JOIN top_students AS ts ON vp.name = ts.name; |
Notice how the data from the two tables is combined:
1 2 3 4 5 6 | id | name | id | name ----+-----------------+----+----------------- 3 | Caroline Carter | 3 | Caroline Carter 1 | Diana Hoffman | 1 | Diana Hoffman 2 | Gregory Colon | 4 | Gregory Colon (3 ROWS) |
PostgreSQL left join
For this example, we will use a LEFT JOIN
FROM
the varsity_players
with the top_students
on a name match. This LEFT JOIN will guarantee that all the varsity_players
will be listed and will only populate the top_students
data if there is a match on name.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT vp.id, vp.name, ts.id, ts.name FROM varsity_players AS vp LEFT JOIN top_students AS ts ON vp.name = ts.name; id | name | id | name ----+-----------------+----+----------------- 3 | Caroline Carter | 3 | Caroline Carter 1 | Diana Hoffman | 1 | Diana Hoffman 5 | Geoffrey Cortes | | 2 | Gregory Colon | 4 | Gregory Colon 4 | Peter Vazquez | | (5 ROWS) |
PostgreSQL right join
For this example, we will list all the top_students
and the varsity_players
that are top_students
as well using the RIGHT JOIN
, see the following:
1 2 3 4 | SELECT vp.id, vp.name, ts.id, ts.name FROM varsity_players AS vp RIGHT JOIN top_students AS ts ON vp.name = ts.name; |
The above will return the following table:
1 2 3 4 5 6 7 8 | id | name | id | name ----+-----------------+----+----------------- 3 | Caroline Carter | 3 | Caroline Carter | | 5 | Cathy England 1 | Diana Hoffman | 1 | Diana Hoffman | | 2 | Ernest Blake 2 | Gregory Colon | 4 | Gregory Colon (5 ROWS) |
Notice how the IDs from the top_students
table are joined with the varsity_players
table’s column records.
PostgreSQL full outer join
For this example, we will list all the varsity players and all the top students using the FULL OUTER JOIN
, see the following:
1 2 3 4 | SELECT vp.id, vp.name, ts.id, ts.name FROM varsity_players AS vp FULL OUTER JOIN top_students AS ts ON vp.name = ts.name; |
The above statement will match up and combine the name
columns for each table and will look the following:
1 2 3 4 5 6 7 8 9 10 | id | name | id | name ----+-----------------+----+----------------- 3 | Caroline Carter | 3 | Caroline Carter | | 5 | Cathy England 1 | Diana Hoffman | 1 | Diana Hoffman | | 2 | Ernest Blake 5 | Geoffrey Cortes | | 2 | Gregory Colon | 4 | Gregory Colon 4 | Peter Vazquez | | (7 ROWS) |
Conclusion
We hope you’ve found this article on how to perform a PostgreSQL join on the ObjectRocket platform informative. We covered the most common joins and showed examples of each so you can see which type suits your situation. If you don’t have a database already, getting your free PostgreSQL database setup in ObjectRocket is easy and it’s just as easy to connect to.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started