PostgreSQL: FROM Clause

In this PostgreSQL post explains how to use the PostgreSQL FROM clause with syntax and examples.

Description

The PostgreSQL FROM clause is used to list the tables and any join information required for the query in PostgreSQL.

Syntax

The syntax for the FROM clause in PostgreSQL is:

FROM table1
[ { INNER JOIN
  | LEFT OUTER JOIN
  | RIGHT OUTER JOIN
  | FULL OUTER JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

The tables used in the PostgreSQL statement. The two tables are joined based on table1.column1 = table2.column1.

Note

• When using the FROM clause in a PostgreSQL statement, there must be at least one table listed in the FROM clause.
• If there are two or more tables listed in the PostgreSQL FROM clause, these tables are generally joined using INNER or OUTER joins, as opposed to the older syntax placed in the WHERE clause.

Example - With one table

It is difficult to explain the syntax for the PostgreSQL FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM categories
WHERE category_id >= 2500
ORDER BY category_id ASC;

In this PostgreSQL FROM clause example, we've used the FROM clause to list the table called categories. There are no joins performed since we are only using one table.

Example - Two tables with INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT products.product_name, categories.category_name
FROM categories
INNER JOIN products
ON categories.category_id = products.category_id
ORDER BY products.product_name;

This PostgreSQL FROM clause example uses the FROM clause to list two tables - categories and products. And we are using the FROM clause to specify an INNER JOIN between the categories and products tables based on the category_id column in both tables.

Example - Two Tables with OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT categories.category_name, products.product_id
FROM categories
LEFT OUTER JOIN products
ON categories.category_id = products.category_id
WHERE categories.status = 'Active'
ORDER BY categories.category_name;

This PostgreSQL FROM clause example uses the FROM clause to list two tables - categories and products. And we are using the FROM clause to specify a LEFT OUTER JOIN between the categories and products tables based on the category_id columns in both tables.