JOINS

JOINS will allow us to combine information from multiple tables

AS

Before we learn about JOINs , let’s quickly cover the AS clause which allows us to create an “alias” for a column or result.

syntax:

●SELECT column AS new_name

FROM table

●SELECT SUM(column) AS new_name

FROM table

Example:

The AS operator gets executed at the very end of a query, meaning that we can not use the ALIAS inside a WHERE operator.

INNER JOIN:

There are several types of JOINs, in this part of medium post we will go through the simplest JOIN type, which is an INNER JOIN.

What is a JOIN operation?

JOINs allow us to combine multiple tables together.

The main reason for the different JOIN types is to decide how to deal with information only present in one of the joined tables.

Let’s imagine a simple example:

Our company is holding a conference for people in the movie rental industry. We’ll have people register online beforehand and then login the day of the conference.

After the conference we have these tables:

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

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

To help you keep track, Registrations names’ first letters go A,B,C,D

An INNER JOIN will result with the set of records that match in both tables.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match = TableB.col_match

SELECT * FROM TableB
INNER JOIN TableA
ON TableA.col_match = TableB.col_match

SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name

Remember that table order won’t matter in an INNER JOIN.

Also if you see just JOIN without the INNER, PostgreSQL will treat it as an INNER JOIN.