SQL JOINS in PostgreSQL

Joins are a way to combine multiple tables into a single one. There are many types of joins available to us. It is very important to understand each type as it is unique and so is its purpose. Let us talk about each one in detail.

Types of Joins:

• Inner Join

• Outer Join

• Left Outer Join

• Right Outer Join

• Outer Join (Exclusive)

• Left Outer Join (Exclusive)

• Right Outer Join (Exclusive)

Why so many?

We do have several different types of joins, but each one serves its own purpose. Inner, outer and left outer joins are the main among the available joins and the rest are either obtained by changing the order of the tables or by adding a filter to the main joins. Don't worry if you don't see this point now. At the end, you will, for sure. Let's jump into the details.

Details:

We are going to use two tables, Registrations and Logins. Registration table contains students who registered for an event and the Logins table has students who attended the event. In this example, to keep it simple, we will assume that the names are unique.

Notice that Ami and Beth have registered and also attended the event, so they appear in both tables, whereas Chala and Debbie from the Registrations table are a no show. From the Logins table, we have Zen and Nala who have not registered, but attended the event. We have some discrepancy in our tables and lets see how each join takes care of such cases.

INNER JOIN: return the common rows to both tables.

Query:

We are selecting everything (* ) from the first table (Registrations) joining (INNER JOIN) our second table (Logins) based on (using keyword ON), here we need to specify on what common column we are joining these two tables (name in this example). This Name column acts as a reference point when combining the tables.

Result of our query:

We got Ami and Beth from table one along with their registration ids and since there was a match in the Logins table, we got login id too.

We got only our matches as a result of our inner join query. Note that INNER JOIN or just JOIN are same in postgreSQL

Notice that we have name column appeared twice in the output, that is because we selected everything indicated by * in our query. To avoid this duplication, we can specify the desired column names in our query.

Query to get rid of the duplication:

Result of our query:

OUTER JOINS:

Here we will be dealing with matches as well as the discrepancies in our tables. There are several different outer joins,let's start with the simplest one.

FULL OUTER JOIN: It returns every row from the first table as well as the second table.

Query:

Result of our query:

As a result of our query, we got everything from our Registrations table and since Ami and Beth have matches we got them along with their corresponding login ids. Also, since Chala and Debbie have no matches in the Logins table, SQL fills them with null for us, indicating the no match situation. Because it's an outer join, we also got Zen and Nala from the Logins table and because they don’t have a match in the Registrations table, we have nulls indicating the no match situation again.

FULL OUTER JOIN (EXCLUSIVE): It returns all the rows that are unique to the first table and the rows that are unique to the second table. This is the exact opposite of inner join. It excludes the matches.

Query: The way we achieve this query is, we start with full outer join and add a filter with a WHERE clause. Since we want the rows that are not common, we are looking for the no match rows.

Result of our query:

As a result we got Chala and Debbie from our Registrations table, since they don't have matchings in the Logins table along with Zen and Nala as they also don’t have matchings in the Registrations table. We don’t see Amy and Beth because they have matching and we are excluding the common ones in this query.

LEFT OUTER JOIN:

It returns every row from the left table and also the matching rows with the right table. The rows that are unique to the second table will not appear in the result.

Query:

Result of our query:

As a result of our query, we have all the students from our left table, Registrations and also the matching ones from the right table, Logins. Amy and Beth have matches, so we got them with their corresponding login ids. Additionally, we got Chala and Debbie as they are exclusive to the Registrations table and since they don’t have matches in the Logins, we have null filled in to indicate the no match situation.

LEFT OUTER JOIN (EXCLUSIVE):

It returns the exclusive rows to the left table. It eliminates the common ones as well as the exclusive rows to the right table.

Query;

Result:

As a result of our query, we have the rows that are exclusive to our left table. So we got Chala and Debbie from Registrations who don't have a match in the right table, Logins. Notice that the query omitted Ami and Beth as they are the common ones as well as Zen and Nala, because they are unique to the right table, Logins.

RIGHT OUTER JOIN:

It returns all the rows from the right table and also the common ones with the left table. It excludes the rows that are unique to the left table.

Query:

Result of our query:

As a result of our query, we have all the rows from our right table, Logins and also the common ones with the Registrations table(left). So we got Ami and Beth along with their corresponding registration ids and also Zen and Nala which are unique to our Logins table and got nulls as they don’t have matchings in the Registrations. Chala and Debbie do not appear in the result as they are exclusive to the left table.

RIGHT OUTER JOIN (EXCLUSIVE):

It returns all the rows that are exclusive to the right table. It omits the common ones as well as the ones that are exclusive to the left table.

Query:

Result of our query:

As a result of our query, we only got Zen and Nala as they are unique to our right table, Logins. Ami and Beth were not taken here as they are common to both tables and also Chala and Debbie were omitted as they are unique to our left table, Registrations.

RIGHT OUTER OR LEFT OUTER?

Choosing one of these joins depends on what you have on your mind, which table you are considering as left and which one right. If you observe closely, you can obtain one from the other by switching the tables. Try this query and see if you can eliminate one from the list of many available joins.

Query to try:

Conclusion:

Now we know what joins are in SQL, how each join is different and how to use them effectively to get back the desired information. We only have 3 main ones, INNER, OUTER, and LEFT/RIGHT OUTER. The rest of them can be derived from the main ones either by switching the order of the tables or by applying additional filters to the main ones. Know the tools available and use the right ones.