Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial
/Getting Started/Natural Join

PostgreSQL NATURAL JOIN

Summary: in this tutorial, you will learn how to use the PostgreSQLNATURAL JOIN to query data from two tables.

Introduction to PostgreSQL NATURAL JOIN clause

A natural join is ajoin that creates an implicit join based on the same column names in the joined tables.

The following shows the syntax of the PostgreSQL NATURAL JOIN clause:

SELECT select_listFROM table1NATURAL [INNER,LEFT, RIGHT] JOIN table2;

In this syntax:

  • First, specify columns from the tables from which you want to retrieve data in theselect_list in theSELECT clause.
  • Second, provide the main table (table1) from which you want to retrieve data.
  • Third, specify the table (table2) that you want to join with the main table, in theNATURAL JOIN clause.

A natural join can be aninner join,left join, orright join. If you do not specify an explicit join, PostgreSQL will use theINNER JOIN by default.

The convenience of theNATURAL JOIN is that it does not require you to specify the condition in the join clause because it uses an implicit condition based on the equality of the common columns.

The equivalent of theNATURAL JOIN clause will be like this:

SELECT select_listFROM table1[INNER, LEFT, RIGHT]JOIN table2   ON table1.column_name= table2.column_name;

Inner Join

The following statements are equivalent:

SELECT select_listFROM table1NATURAL INNER JOIN table2;

And

SELECT select_listFROM table1INNER JOIN table2 USING (column_name);

Left Join

The following statements are equivalent:

SELECT select_listFROM table1NATURAL LEFT JOIN table2;

And

SELECT select_listFROM table1LEFT JOIN table2 USING (column_name);

Right join

The following statements are equivalent:

SELECT select_listFROM table1NATURAL RIGHT JOIN table2;

And

SELECT select_listFROM table1RIGHT JOIN table2 USING (column_name);

Setting up sample tables

The following statements createcategories andproducts tables, and insert sample data for the demonstration:

CREATE TABLE categories (  category_id SERIAL PRIMARY KEY,  category_name VARCHAR (255) NOT NULL);CREATE TABLE products (  product_id serial PRIMARY KEY,  product_name VARCHAR (255) NOT NULL,  category_id INT NOT NULL,  FOREIGN KEY (category_id) REFERENCES categories (category_id));INSERT INTO categories (category_name)VALUES  ('Smartphone'),  ('Laptop'),  ('Tablet'),  ('VR')RETURNING *;INSERT INTO products (product_name,category_id)VALUES  ('iPhone', 1),  ('Samsung Galaxy', 1),  ('HP Elite', 2),  ('Lenovo Thinkpad', 2),  ('iPad', 3),  ('Kindle Fire', 3)RETURNING *;

Theproducts table has the following data:

product_id |  product_name   | category_id------------+-----------------+-------------          1 | iPhone          |           1          2 | Samsung Galaxy  |           1          3 | HP Elite        |           2          4 | Lenovo Thinkpad |           2          5 | iPad            |           3          6 | Kindle Fire     |           3(6 rows)

Thecategories table has the following data:

category_id | category_name-------------+---------------           1 | Smartphone           2 | Laptop           3 | Tablet           4 | VR(4 rows)

PostgreSQL NATURAL JOIN examples

Let’s explore some examples of using theNATURAL JOIN statement.

1) Basic PostgreSQL NATURAL JOIN example

The following statement uses the NATURAL JOIN clause to join the products table with the categories table:

SELECT *FROM productsNATURAL JOIN categories;

This statement performs an inner join using thecategory_id column.

Output:

category_id | product_id |  product_name   | category_name-------------+------------+-----------------+---------------           1 |          1 | iPhone          | Smartphone           1 |          2 | Samsung Galaxy  | Smartphone           2 |          3 | HP Elite        | Laptop           2 |          4 | Lenovo Thinkpad | Laptop           3 |          5 | iPad            | Tablet           3 |          6 | Kindle Fire     | Tablet(6 rows)

The statement is equivalent to the following statement that uses the INNER JOIN clause:

SELECT*FROM productsINNER JOIN categories USING (category_id);

2) Using PostgreSQL NATURAL JOIN to perform a LEFT JOIN

The following example uses theNATURAL JOIN clause to perform aLEFT JOIN without specifying the matching column:

SELECT *FROM categoriesNATURAL LEFT JOIN products;

Output:

category_id | category_name | product_id |  product_name-------------+---------------+------------+-----------------           1 | Smartphone    |          1 | iPhone           1 | Smartphone    |          2 | Samsung Galaxy           2 | Laptop        |          3 | HP Elite           2 | Laptop        |          4 | Lenovo Thinkpad           3 | Tablet        |          5 | iPad           3 | Tablet        |          6 | Kindle Fire           4 | VR            |       null | null(7 rows)

3) Example of using NATURAL JOIN that causes an unexpected result

In practice, you should avoid using theNATURAL JOIN whenever possible because sometimes it may cause an unexpected result.

Consider the followingcity andcountry tables from thesample database:

Both tables have the samecountry_id column so you can use theNATURAL JOIN to join these tables as follows:

SELECT *FROM cityNATURAL JOIN country;

The query returns an empty result set.

The reason is that both tables have another common column calledlast_update. When theNATURAL JOIN clause uses thelast_update column, it does not find any matches.

Summary

  • Use the PostgreSQLNATURAL JOIN clause to query data from two or more tables that have common columns.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp