PostgreSQL NATURAL JOIN

NATURAL JOIN creates an implicit join condition by automatically matching all column names that appear in both tables. You do not write an ON or USING clause — PostgreSQL infers the condition from the schema at query planning time. While this can make simple queries more concise, it is fragile in production code because any schema change that introduces a new shared column name silently alters the join.

Syntax

SELECT select_list
FROM table1
NATURAL [INNER | LEFT | RIGHT] JOIN table2;

Without an explicit join-type keyword, NATURAL JOIN defaults to NATURAL INNER JOIN. The shared column(s) appear only once in the output — not duplicated from both tables.

Equivalent explicit forms:

-- NATURAL INNER JOIN is equivalent to:
SELECT select_list FROM t1 INNER JOIN t2 USING (shared_col);

-- NATURAL LEFT JOIN is equivalent to:
SELECT select_list FROM t1 LEFT JOIN t2 USING (shared_col);

Practical Example

The following schema tracks software releases and the build pipelines that produce them. Both tables share a pipeline_id column that acts as the natural join key.

CREATE TABLE build_pipelines (
  pipeline_id   SERIAL PRIMARY KEY,
  pipeline_name VARCHAR(100) NOT NULL,
  environment   VARCHAR(30)   NOT NULL   -- 'staging', 'production'
);

CREATE TABLE release_artifacts (
  artifact_id   SERIAL PRIMARY KEY,
  pipeline_id   INT REFERENCES build_pipelines(pipeline_id),
  version_tag   VARCHAR(30)  NOT NULL,
  artifact_size BIGINT,
  built_at      TIMESTAMPTZ DEFAULT now()
);

INSERT INTO build_pipelines (pipeline_name, environment) VALUES
  ('core-api-build',     'production'),
  ('frontend-build',     'production'),
  ('integration-tests',  'staging');

INSERT INTO release_artifacts (pipeline_id, version_tag, artifact_size) VALUES
  (1, 'v2.4.0', 52428800),
  (1, 'v2.4.1', 52530000),
  (2, 'v1.9.0', 12582912),
  (2, 'v1.9.1', 12600000);
-- pipeline 3 (integration-tests) has no artifacts

Basic NATURAL JOIN

SELECT
  pipeline_name,
  environment,
  version_tag,
  artifact_size
FROM build_pipelines
NATURAL JOIN release_artifacts
ORDER BY pipeline_name, version_tag;

PostgreSQL automatically joins on pipeline_id because it is the only column that appears in both tables. pipeline_id appears once in the output.

NATURAL LEFT JOIN — include pipelines with no artifacts

SELECT
  pipeline_name,
  environment,
  version_tag
FROM build_pipelines
NATURAL LEFT JOIN release_artifacts
ORDER BY pipeline_name;

The integration-tests pipeline (no artifacts) now appears with NULL in version_tag.

Inspect what columns NATURAL JOIN will use

Before running a NATURAL JOIN, check the shared column names to confirm the implicit condition is what you intend:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'build_pipelines'
  AND column_name IN (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'release_artifacts'
  );

NATURAL JOIN vs JOIN USING vs INNER JOIN ON

Why NATURAL JOIN Can Fail Silently

Adding a column updated_at to both tables for audit purposes creates a new shared column name that NATURAL JOIN will silently incorporate:

ALTER TABLE build_pipelines   ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE release_artifacts ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now();

After this migration, NATURAL JOIN joins on both pipeline_id AND updated_at. Because the two updated_at timestamps are unlikely to match exactly, most rows return zero results — with no error or warning. This is why JOIN USING (pipeline_id) is the safer choice.

Testing with Vela

Because NATURAL JOIN is sensitive to schema changes, it is a prime candidate for testing on a branch before deploying a migration. Vela’s database branching lets you add a column to both tables on a production-data branch, run the affected queries, and verify that NATURAL JOIN behavior has not changed unexpectedly — before the migration reaches production.

Production Tips

• Prefer INNER JOIN ... ON or JOIN USING (column) over NATURAL JOIN for all production queries — they are explicit, stable, and unaffected by future schema additions.
• Before using NATURAL JOIN on any pair of tables, query information_schema.columns to confirm exactly which columns are shared.
• NATURAL JOIN removes the SELECT * column duplication that ON joins produce for shared columns — if that is the only benefit you need, use JOIN USING instead for the same deduplication with explicit safety.
• An empty result from a NATURAL JOIN on tables you expect to match is almost always caused by an unintended shared column name — inspect with \d tablename in psql to check.
• If you inherit a codebase that uses NATURAL JOIN, migrate to explicit JOIN USING or JOIN ... ON during your next refactoring pass to eliminate the fragility.
• Document any intentional use of NATURAL JOIN with a comment explaining which column(s) it is currently joining on, so the implicit assumption is visible to future maintainers.