NATURAL JOIN in SQL

UpdatedMarch 2, 2026

•1 min read

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified

Another lesser known JOIN - the natural join. But maybe the NATURAL JOIN is not as obscure after all, since it has its own keyword, at least in a couple of SQL dialects - see PostgreSQL portrayed below (sorry, it's not supported in BigQuery, but it does recognize it).

So what's special about it? Well, it joins the tables based on columns that have the same name (and datatype) in the two tables. That is, we don't need to specify any join conditions.

Watch out because if there are no columns with the same name and datatype, it defaults to a Cartesian product is produced (which is what CROSS JOIN does).

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Enjoyed this? Here are some related articles you might find useful:

Comments (2)

Join the discussion

Constantin Lungu1y ago

Thanks for reading!

chandravathi Yerramasu1y ago

It was very beneficial and how to deal the data regarding to requirements, by this article I came to know usage of a particular clause or function accordingly, thanks for providing these kind of articles.

Practical SQL

Part 29 of 50

Short, practical posts on SQL and BigQuery — from core language features to advanced query patterns. A reference for data practitioners at every level.

Up next

Short, almost non-technical guide to SQL query tuning as a Data Engineer

It's not all about those fancy details.

More from this blog

BigQuery Saves Your Query Results — Here's How to Find Them

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

Mar 5, 20261 min read

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

Feb 7, 20261 min read

Flattening JSON arrays in BigQuery

I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN(). It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are. So where is this actually useful?➡️ Ha...

Dec 7, 20251 min read

Using LAST_VALUE with STRUCTS

Even an “empty” STRUCT is still technically something. Not the same as a standalone NULL value. This is why, if you work with STRUCTs in SQL and try to find the latest non-empty struct using LAST_VALUE(...) IGNORE NULLS, you’ll notice it doesn’t help...

Oct 5, 20251 min read

Table grain quick validation with SQL

I was doing some exploratory data analysis on a number of tables I didn’t have much information about and, unfortunately, didn’t know their grain. I needed a quick way to validate my assumptions about the table grain, identify contradicting observati...

Oct 4, 20251 min read

Datawise: A blog on SQL, BigQuery, Analytics, Python Tips and Tricks

204 posts

Data Engineer with a passion for transforming complex data landscapes into insightful stories. Here on my blog, I share insights, challenges, and the ever-evolving dance of technology and business.

Command Palette

Comments (2)

Practical SQL

Short, almost non-technical guide to SQL query tuning as a Data Engineer

More from this blog