NATURAL JOIN in SQL

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).

WITH stores AS (
SELECT 1 AS store_id, 'Flagship store - NY' AS store_name UNION ALL
SELECT 2 AS store_id, 'Main St. - LA' AS store_name UNION ALL
SELECT 3 AS store_id, 'Michigan Ave. - Chicago' AS store_name ),

employees AS (
SELECT 1 AS employee_id, 1 AS store_id UNION ALL
SELECT 2 AS employee_id, 2 AS store_id UNION ALL
SELECT 3 AS employee_id, 3 AS store_id
)

SELECT employee_id, store_id, store_name
FROM stores
NATURAL JOIN employees

---

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

• Self-joins in SQL
• Anti-joins in SQL
• SEMI-JOINS in SQL
• NON-EQUI joins in SQL