PostgreSQL Join

Preamble

PostgreSQL JOIN is used to extract data from multiple tables. PostgreSQL JOIN is executed whenever two or more tables are combined in an SQL statement.

There are different types of PostgreSQL connections:

• PostgreSQL INNER JOIN (or sometimes called a simple connection)
• PostgreSQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• PostgreSQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• PostgreSQL FULL OUTER JOIN (or sometimes called FULL JOIN)

So, let’s discuss the JOIN syntax in PostgreSQL, take a look at the visual illustrations of JOIN in PostgreSQL, and look at the JOIN examples.

INNER JOIN (simple connection)

Most likely, you have already written a query that uses PostgreSQL INNER JOIN. This is the most common type of connection. INNER JOIN returns all rows from multiple tables where the connection condition is met.

The syntax for INNER JOIN in PostgreSQL

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

On this visual diagram, PostgreSQL INNER JOIN returns the shaded area:
PostgreSQL INNER JOIN will return records where table1 and table2 intersect.

Here is an example of INNER JOIN PostgreSQL

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON.supplier_id = orders.supplier_id;

This PostgreSQL INNER JOIN example will return all rows from the suppliers and orders tables where the corresponding value supplier_id is present in the suppliers and orders tables.

Let’s take a look at some data to explain how internal connections work:
We have a table of suppliers with two fields (supplier_id and supplier_name).

It contains the following data:

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the PostgreSQL SELECT operator (which contains INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON.supplier_id = orders.supplier_id;

Our result set will look like this:

Rows for ‘Microsoft’ and ‘NVIDIA’ from the supplier table will be omitted because 10002 and 10003 supplier_id do not exist in both tables. Row for 500127 (order_id) from the orders table will be omitted because supplier_id 10004 does not exist in the supplier’s table.

Old syntax

In conclusion, it’s worth mentioning that the INGER JOIN PostgreSQL example above can be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN

Another type of connection is called PostgreSQL LEFT OUTER JOIN. This type of connection returns all rows from tables with a left-hand connection specified in the ON condition, and only those rows from another table where the fields to be joined are equal (the connection condition is fulfilled).

The syntax for PostgreSQL LEFT OUTER JOIN

SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

Visual Illustration

On this visual diagram, PostgreSQL LEFT OUTER JOIN returns the shaded area:

PostgreSQL LEFT OUTER JOIN will return all records from table1 and only those records from table2 that intersect with table1.

An example of PostgreSQL LEFT OUTER JOIN

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON.supplier_id = orders.supplier_id;

In this example, the LEFT OUTER JOIN will return all rows from the employee’s table and only those rows from the orders table where the combined fields are equal.

PostgreSQL: Inner Joins | Course

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.