PostgreSQL Join for ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

JoinDescription
Inner Joinreturn all rows that matches on the tables
Left Joinreturn all the records from the left table and the records from the right table that matches the left table
Right Joinreturn all the records from the right table and the records from the right left that matches the right table
Full Outer Joinreturn all rows from both tables
Cross Joinreturn the rows from the tables like Cartesian Product
Natural Joinreturn the rows that has the same values on each column
Self-Joinjoined 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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.