Database — Intro

PostgreSQL Join Tables — #DBSeries # Episode 00

Published in

Jungletronics

7 min readSep 27, 2020

Let’s imagine a simple example:

Our company is holding a conference for people in the movie rental industry.

fig 1. Corporate Meeting Conference: Movie Rental Industry!

We´ll have people register online beforehand (registration db) and log on the day of the conference (login db).

Fig 2. Simple tables to work with. I used DbVisualizer Free 11.0.5 [Build #3113]

The registration is simply just providing their name and we’ll keep track of people as they register and then the actual day of the conference they’re going come and log in through an iPad with their name.

The respective id columns indicate what order they registered or logged in on-site.

For the sake of simplicity, we assume the names are unique.

To help you keep track, registration’s name first letter goes A, B, C, and D.

So after the conference, we end up having these tables (see code memory at the very end of this post:)

Fig 3. Make a mark on those who registered and attended the conference, because they…

Fig 4. …appeared in both tables A and B \o/

So the situation we have right now is in the registration table:

We had Andrew, Bob, Charlie, and David (Fig 3) registered for the conference.

We send a promotional kit to each of them.

However, the actual day of the conference when people were logging in we only had Andrew and Bob showed up (Fig 4) that actually registered, and then Xavier and Yolanda, who forgot to register they came on-site and then logged in.

Notice here that Charlie and David registered but didn’t end up showing up to the conference to log in:/

So we have that discrepancy there.

INNER JOINS

So an INNER JOIN is going to able to join these tables together for records that are a match in both.

So when we’re talking about an INNER JOIN we’re only looking for the set of records that happen to be in both tables (see right in the center of the figure below).

Here is the Venn Diagrams to help us with that:

Now, Let’s suppose we want to know the clients that make everything correct, as we’d expected:

Clients that register themselves previouslly and showed up at the conference's day!

Fig 6. Here is how to seek them out! INNER JOIN!

Congratulations to Andrew and Bob o/

Now something to keep in mind especially when dealing with INNER JOIN:

Please notice that this Venn diagram essentially is only filled in in the crossover middle essentially stuff that can be found in both of these circles.

By the way, INNER JOIN is SYMMETRICAL!

Let’s confirm that?

Fig 7. there is no problem in changing the position of the tables. It’s like a mirror! Look …

See, the result is the same!

So then we look at where do we have a match in both tables.

Meaning that whoever registered and appeared at the conference.

For those we can give, for example, the Full Code Conference Videos access, encouraging everyone to register for the next event, so that we can have all the data of those who participate in our’s company conferences.

Now, Let's clear the table up and knowing exactly the id_reg of each one of the Premmium Customers!

Use this query:

Fig 8. We’re only looking at the intersection of tables A and B of that Venn Diagram

As reg_id belongs only to registration, there is no need to specify its path.

However, the name is present in both tables, so, use registration.name to address what register we want to query.

Another thing: INNER JOIN is the default. See:

Fig 9. But is highly recommended that we use **INNER JOIN** for readability!

By not registering which JOIN you end up having your code loses in readability! So, please, use INNER JOIN!

Better to be assertive. It do someone no harm!

So far, so good; just fine!

OUTER JOINS

Ok, let’s know more about the behavior of our clients:

Now we just want to know who just appears in one of the tables.

Now the subject is OUTER!

OUTER JOINS essentially allows us to specify how to deal with values only present in one of the tables being joined.

Here we are talking about the LEFT and RIGHT [OUTER] JOIN:

FULL [OUTER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN

Let’s suppose we want to know who forgot to register but they still showed up and logged in anyways.

These are potential loyal customers, right?

Fig 10. See that these customers gave us a disservice :/

Fig 11. But these ones are just potential loyal customers o/

For these clients, we must look for the names in the table login.

Xavier and Yolanda forgot to previously register.

How to rescue them?

Let’s initialize by knowing the whole; to get there:

FULL OUTER JOIN!

Fig 12. When we unite everything, fields with NULL value appear!

Here we can readily see that there are data that belongs to one table but not to both.

So far, so fine!

What we want now is the data that appears on the right, right?

These are the customers who do not have reg_id (where reg_id is NULL).

Our potential loyal customers!

Here is the answer:

Fig 13. RIGHT OUTER JOIN only searches for data that appears in the RIGHT table. Note, this is SYMMETRIC, The order does matter!

Only Xavier and Yolanda were present but did not register.

Now we want to know who registered but did not attend, causing businesses waste of time and money for our company since we sent them a Conference‘s PREMIUM KIT, such as shirts, bottoms, and promotional stickers:/

Fig 14. LEFT OUTER JOIN only searches for data that appears in the LEFT table. Remember, this is SYMMETRIC, The order does matter!

Charlie and David must be removed from the company’s VIP list.

And Xavier and Yolanda must join the list as the VIP’s welcomers \o/.

For the sake of completeness, I should also mention what constitutes the exact opposite of the INNER JOIN:

FULL OUTER JOIN

Shall we see more of this fundamental concept to finalize this post?

Well, here you have it!

Our limited universe:

Fig 15. Pure FULL OUTER JOIN. Returns everything!

Now we can further qualify this with a where statement. If we filter, then:

Fig 16. FULL OUTER JOIN only searches for data that appears exclusively on both tables.

And that’s all!

Neatly explained, don’t you think?

Yeah, I think so...

Please note that the credits MUST go to Jose Portilla in his awesome course The Complete SQL Bootcamp 2020: Go from Zero to Hero.

I recommend that you enroll, like me, in one of his courses.

It is simply FANTASTIC!

Thank you, Mr. Portilla!

I’m a fan of Jose Portilla!

I bought his courses in Statistics, Python, Django, and Db!

Anyway, thank you for reading this post!

Bye and I see you in the next DBSeries! Comments are welcomed o/

See you there! Bye!

Code Memory (from pgAdmin 4 Version 4.26):

I’m using psql shell:

1 - CREATE DATABASE academy;
2 - USE academy;
3 - CREATE TABLE registration (
             reg_id serial PRIMARY KEY,
             name VARCHAR (50) UNIQUE NOT NULL
                           );
4 -  CREATE TABLE login(
             log_id serial PRIMARY KEY,
             name VARCHAR (50) UNIQUE NOT NULL
                           );

Now go to GUI pgAdmin 4:

5 - INSERT INTO registration (reg_id, name) VALUES ( 1, 'Andrew');
    INSERT INTO registration (reg_id, name) VALUES ( 2, 'Bob');
    INSERT INTO registration (reg_id, name) VALUES ( 3, 'Charlie');
    INSERT INTO registration (reg_id, name) VALUES ( 4, 'David');6 - INSERT INTO login (log_id, name) VALUES ( 1, 'Xavier');
    INSERT INTO login (log_id, name) VALUES ( 2, 'Andrew');
    INSERT INTO login (log_id, name) VALUES ( 3, 'Yolanda');
    INSERT INTO login (log_id, name) VALUES ( 4, 'Bob');7 - SELECT * FROM registration;8 - SELECT * FROM login;9 - SELECT * FROM registration
             INNER JOIN login
             ON registration.name = login.name;10 - SELECT * FROM login
             INNER JOIN registration
             ON registration.name = login.name;11 - SELECT reg_id, registration.name FROM login
             INNER JOIN registration
             ON registration.name = login.name;12 - SELECT * from registration
              FULL OUTER JOIN login
              ON registration.name = login.name13 - SELECT log_id, registration.name from registration
              RIGHT OUTER JOIN login
              ON registration.name = login.name
              WHERE registration.name IS NULL;14 - SELECT reg_id, registration.name from registration
               LEFT OUTER JOIN login
               ON registration.name = login.name
               WHERE login.name IS NULL;15 - SELECT * FROM registration
               FULL OUTER JOIN login
               ON registration.name = login.name;16 - SELECT * FROM registration 
               FULL OUTER JOIN login
               ON registration.name = login.name
               WHERE reg_id IS NULL OR
                     log_id IS null;

Credits & References

The Complete SQL Bootcamp 2020: Go from Zero to Hero — Become an expert at SQL! by Jose Portilla

A Visual Explanation of SQL Joins by CODING HORROR

SQL Joins Explained by sql-join.com

Join (SQL) by wikipedia.org

PostgreSQL — official documentation book — https://docstore.mik.ua/manuals/sql/postgresql-8.2.6/preface.html