{"id":349,"date":"2024-11-26T15:24:29","date_gmt":"2024-11-26T08:24:29","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=349"},"modified":"2025-01-02T10:08:32","modified_gmt":"2025-01-02T03:08:32","slug":"postgresql-natural-join","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-natural-join\/","title":{"rendered":"PostgreSQL Natural Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use PostgreSQL NATURAL JOIN<\/code> to join tables based on columns with the same names.<\/p>\n\n\n\n

An Insight Into PostgreSQL NATURAL JOIN #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a NATURAL JOIN<\/code> allows you to join tables based on columns with the same names in both tables.<\/p>\n\n\n\n

Here’s the syntax of the NATURAL JOIN<\/code>:<\/p>\n\n\n

SELECT<\/span> select_list\nFROM<\/span> table1\nNATURAL<\/span> [INNER<\/span>, LEFT, RIGHT, FULL<\/span>] JOIN<\/span> table2;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
The NATURAL JOIN<\/code> joins two tables, table1<\/code>and table2<\/code>. These tables need the same column name and compatible types for matching.<\/p>\n\n\n\n
If you don’t specify a join after the NATURAL<\/code> keyword, PostgreSQL will use an INNER JOIN<\/code> by default. If table1<\/code> and table2<\/code> have no common column, the NATURAL JOIN<\/code> behaves like a CROSS JOIN<\/code>.<\/p>\n\n\n\n
Setting up sample tables #<\/a><\/h2>\n\n\n\n
First, create a new table<\/a> called brands<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> brands (\n  brand_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  brand_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  brands (brand_name)\nVALUES<\/span>\n  ('Apple'<\/span>),\n  ('Samsung'<\/span>),\n  ('Google'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 brand_id | brand_name\n----------+------------<\/span>\n        1<\/span> | Apple\n        2<\/span> | Samsung\n        3<\/span> | Google<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, create a new table called products<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> products (\n  product_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  product_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  price DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  brand_id INT<\/span>,\n  FOREIGN KEY<\/span> (brand_id) REFERENCES<\/span> brands (brand_id)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, insert rows into the products<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (product_name, price, brand_id)\nVALUES<\/span>\n  ('iPhone 14 Pro'<\/span>, 999.99<\/span>, 1<\/span>),\n  ('iPhone 15 Pro'<\/span>, 1199.99<\/span>, 1<\/span>),\n  ('Galaxy S23 Ultra'<\/span>, 1149.47<\/span>, 2<\/span>),\n  ('Oppo Find Flip'<\/span>, 499.99<\/span>, NULL<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 product_id |   product_name   |  price  | brand_id\n------------+------------------+---------+----------<\/span>\n          1<\/span> | iPhone 14<\/span> Pro    |  999.99<\/span> |        1<\/span>\n          2<\/span> | iPhone 15<\/span> Pro    | 1199.99<\/span> |        1<\/span>\n          3<\/span> | Galaxy S23 Ultra | 1149.47<\/span> |        2<\/span>\n          4<\/span> | Oppo Find Flip   |  499.99<\/span> |     NULL<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL NATURAL JOIN examples #<\/a><\/h2>\n\n\n\n
We’ll use NATURAL JOIN<\/code> to join the brands<\/code> and products<\/code> tables.<\/p>\n\n\n\n
INNER JOIN #<\/a><\/h3>\n\n\n\n
The following statement uses a NATURAL JOIN<\/code> to perform an INNER JOIN<\/code> between the products<\/code> and brands<\/code> tables:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price,\n  brand_name\nFROM<\/span>\n  products\n  NATURAL<\/span> INNER<\/span> JOIN<\/span> brands;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name   |  price  | brand_name\n------------------+---------+------------\n iPhone 14 Pro    |  999.99 | Apple\n iPhone 15 Pro    | 1199.99 | Apple\n Galaxy S23 Ultra | 1149.47 | Samsung<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
LEFT JOIN #<\/a><\/h3>\n\n\n\n
The following example uses a NATURAL JOIN<\/code> to join the products and brands tables using a LEFT JOIN<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price,\n  brand_name\nFROM<\/span>\n  products\n  NATURAL<\/span> LEFT JOIN<\/span> brands;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name   |  price  | brand_name\n------------------+---------+------------\n iPhone 14 Pro    |  999.99 | Apple\n iPhone 15 Pro    | 1199.99 | Apple\n Galaxy S23 Ultra | 1149.47 | Samsung\n Oppo Find Flip   |  499.99 | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
RIGHT JOIN #<\/a><\/h3>\n\n\n\n
The following statement uses a NATURAL JOIN<\/code> to join the products<\/code> and brands<\/code> tables using a RIGHT JOIN<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price,\n  brand_name\nFROM<\/span>\n  products\n  NATURAL<\/span> RIGHT JOIN<\/span> brands;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name   |  price  | brand_name\n------------------+---------+------------\n iPhone 14 Pro    |  999.99 | Apple\n iPhone 15 Pro    | 1199.99 | Apple\n Galaxy S23 Ultra | 1149.47 | Samsung\n NULL             |    NULL | Google<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
FULL JOIN #<\/a><\/h3>\n\n\n\n
The following example uses a NATURAL JOIN<\/code> to join the products<\/code> and brands<\/code> tables using a FULL JOIN<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price,\n  brand_name\nFROM<\/span>\n  products\n  NATURAL<\/span> FULL<\/span> JOIN<\/span> brands;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name   |  price  | brand_name\n------------------+---------+------------\n iPhone 14 Pro    |  999.99 | Apple\n iPhone 15 Pro    | 1199.99 | Apple\n Galaxy S23 Ultra | 1149.47 | Samsung\n Oppo Find Flip   |  499.99 | NULL\n NULL             |    NULL | Google<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL NATURAL JOIN caveats #<\/a><\/h2>\n\n\n\n
PostgreSQL NATURAL JOIN<\/code> relies on the same column names for matching rows. If you change the table structure, the query may work differently than expected. For example:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> products, brands;\n\nCREATE<\/span> TABLE<\/span> brands (\n  brand_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> products (\n  product_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  price DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  brand_id INT<\/span>,\n  FOREIGN KEY<\/span> (brand_id) REFERENCES<\/span> brands (brand_id)\n);\n\nINSERT<\/span> INTO<\/span>\n  brands (name<\/span>)\nVALUES<\/span>\n  ('Apple'<\/span>),\n  ('Samsung'<\/span>),\n  ('Google'<\/span>) \nRETURNING<\/span> *;\n\nINSERT<\/span> INTO<\/span>\n  products (name<\/span>, price, brand_id)\nVALUES<\/span>\n  ('iPhone 14 Pro'<\/span>, 999.99<\/span>, 1<\/span>),\n  ('iPhone 15 Pro'<\/span>, 1199.99<\/span>, 1<\/span>),\n  ('Galaxy S23 Ultra'<\/span>, 1149.47<\/span>, 2<\/span>),\n  ('Oppo Find Flip'<\/span>, 499.99<\/span>, NULL<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
We expect the following query to use the brand_id<\/code> column for joining the two tables:<\/p>\n\n\n
SELECT<\/span>\n  p.name product_name,\n  p.price,\n  b.name brand_name\nFROM<\/span>\n  products p\n  NATURAL<\/span> INNER<\/span> JOIN<\/span> brands b;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, it returns an empty set:<\/p>\n\n\n
 product_name | price | brand_name\n--------------+-------+------------<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that the products<\/code> and brands<\/code> tables have the same brand_id<\/code> and name<\/code> columns. <\/p>\n\n\n\n
In this case, the NATURAL JOIN<\/code> uses the values from both brand_id<\/code> and name<\/code> columns for matching rows. The NATURAL JOIN<\/code> behaves like the following query:<\/p>\n\n\n
SELECT<\/span>\n  p.name product_name,\n  p.price,\n  b.name brand_name\nFROM<\/span>\n  products p\n  INNER<\/span> JOIN<\/span> brands b ON<\/span> b.brand_id = p.brand_id\n  AND<\/span> b.name = p.name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since product names differ from brand names, the join condition is false, which causes the query to return an empty set.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Second, insert rows<\/a> into the brands<\/code> table:<\/p>\n\n\n
The NATURAL JOIN<\/code> can be INNER JOIN<\/a><\/code>, LEFT JOIN<\/a><\/code>, RIGHT JOIN<\/a><\/code>, and FULL JOIN<\/a><\/code>.<\/p>\n\n\n\n