Difference between natural join and inner join

Question

What is the difference between a natural join and an inner join?

Lavernlaverna

This question is not a duplicate of the other, as this is about INNER vs NATURAL joins, which are not addressed in the other. – Foredeck

At one time, this was closed as a duplicate of What is the difference between left, right, outer and inner joins, but that question does not address the difference between inner joins and natural joins. – Lapham

Answer 1

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned.

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

The repeated column is avoided.

(AFAICT from the standard grammar, you can't specify the joining columns in a natural join; the join is strictly name-based. See also Wikipedia.)

(There's a cheat in the inner join output; the a. and b. parts would not be in the column names; you'd just have column1, column2, column1, column3 as the headings.)

Lapham

Not sure I buy this in specific (an INNER JOIN could be constructed with the exact same result set), although it touches on the general: "some magical short-hand behavior". – Shelbyshelden

I have two table TableA(Column1,Column2) and TableB(Column2,Column3). – Harvey

Collapsing columns in the output is the least-important aspect of a natural join. The things you need to know are (A) it automatically joins on fields of the same name and (B) it will f*** up your s*** when you least expect it. In my world, using a natural join is grounds for dismissal. – Foredeck

@JonofAllTrades Can you explain more about what exactly NATURAL JOIN will ruin, why it's unexpected, and what world you're in? – Simian

This is addressed somewhat in user166390's answer. Say you have a natural join between Customers and Employees, joining on EmployeeID. Employees also has a ManagerID field. Everything's fine. Then, some day, someone adds a ManagerID field to the Customers table. Your join will not break (that would be a mercy), instead it will now include a second field, and work incorrectly. Thus, a seemingly harmless change can break something only distantly related. VERY BAD. The only upside of a natural join is saving a little typing, and the downside is substantial. – Foredeck

@Jonathan, Regarding your answer, you stated that SELECT * FROM TableA INNER JOIN TableB USING (Column1) gives 4 columns. This is not correct because SELECT * FROM TableA INNER JOIN TableB USING (Column1) and SELECT * FROM TableA NATURAL JOIN TableB are equal, they both give 3 columns. – Frechette

I donth think that inner join will give you the foreign key column twice. I tested it and that's not what I got. It actually displays it only once. – Saphena

@bangbang: Details? Which DBMS? Which version? Are you sure? – Lapham

@JonathanLeffler I'm also getting twice columns just once in Oracle 12c. using Inner join with USING clause. here is the snapshot imgur.com/VQLDala – Philipphilipa

Plus one. Cool I did not know about (USING) . Bummer, it looks like it's not in SQL Server? – Impassable

Answer 2

• An inner join is one where the matching row in the joined table is required for a row from the first table to be returned
• An outer join is one where the matching row in the joined table is not required for a row from the first table to be returned
• A natural join is a join (you can have either natural left or natural right) that assumes the join criteria to be where same-named columns in both table match

I would avoid using natural joins like the plague, because natural joins are:

• not standard sql [SQL 92] and therefore not portable, not particularly readable (by most SQL coders) and possibly not supported by various tools/libraries
• not informative; you can't tell what columns are being joined on without referring to the schema
• your join conditions are invisibly vulnerable to schema changes - if there are multiple natural join columns and one such column is removed from a table, the query will still execute, but probably not correctly and this change in behaviour will be silent
• hardly worth the effort; you're only saving about 10 seconds of typing

Vergeboard

I think that left/right for outer should be mentioned (since outer is mentioned at all). But otherwise, nice and concise: it's only missing the pretty example SQL record diagrams. – Shelbyshelden

NATURAL LEFT and NATURAL RIGHT also exist. But yes, still avoid them. – Gourmet

@Bohemian, Regarding "avoid them like the plague", there are real use cases for natural joins whereby they come in handy. mariadb.com/kb/en/sql-99/natural-join "...The casual-looking "Books NATURAL JOIN Checkouts" is only possible when database naming conventions are formal and enforced...." – Frechette

"I would avoid using natural joins like the plague" - Can you explain why? – Valiancy

@bad ok - I've added some reasons – Vergeboard

There's a lot wrong with your reasoning. Natural join is Standard SQL and has been since 1992 (ref). Most DBMSs support it I believe. The columns can be specified in a select list as input to the join, in which case the specification of those columns is explicit in the query and is not "vulnerable" to schema changes (unless you drop a column being referenced in which case the query will obviously fail just as any other query would). – Salep

@sqlvovel there's a lot wrong with your comment, specifically it's incorrect. Join columns can't be "specified in a select list". The definition of a natural join is to join on *all like-named columns*. From MySQL doc: The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.. And another thing - in practice it's useless, because id is ubiquitous and useless to join on; usual foreign key names are tablename_id. Natural joins are a bad, bad, bad idea. – Vergeboard

Example: SELECT * FROM (SELECT a,b FROM t1) t1(a,b) NATURAL JOIN (SELECT col1,col2 FROM t2) t2(a,c); My main point is that your "not standard sql" claim is incorrect. – Salep

@sqlvogel subqueries??? Apart from the fact that your query has syntax errors (you can't alias column names like that) and would perform very poorly (2 full scans, plus returned data size is doubled), I fail to see any reason to not use the simpler, shorter and clearer select a, b from t1 join t2 on a = col1 and b = col2 – Vergeboard

My query has no syntax errors and is standard SQL. You will have problems if you are using MySQL / Maria (which have very patchy SQL support) but more compliant DBMSs like Oracle and PostgreSQL will execute my query just fine. You can confirm that with Mimer SQL validator. The query plan should be the same as your inner join version but note that your inner join query is unsafe if the schema changes because you haven't referenced the table names in the SELECT or ON clause. My query is safer and there is no need to do that. – Salep

@sqlvogel I am using MySQL, like most programmers are. I didn't reference tables because given your particular query, they are not not needed. Ignoring my misgivings (except for the doubled returned columns which stands), and adding in aliases, again I say... I fail to see any reason to not use the simpler, shorter and clearer select x.a, x.b from t1 x join t2 y on x.a = y.col1 and x.b = y.col2 – Vergeboard

There are no double returned columns in my query. One of the advantages of the NJ semantics is that duplicated columns are never returned. Your previous query was also "less safe" than mine because it would fail if a column called "a" was added to t2 (because the non-aliased join condition is ambiguous). I suspect that your prejudices against NJ are based on the fact that you haven't tried it in a product where standard SQL is properly supported. The question here is about SQL, not MySQL - quite different things. You still haven't corrected your answer about it being non-standard. – Salep

I like you your answer the best, since it stress the importance of implicit visible code. And avoid explicit, inferred behavior. – Prepotent

Answer 3

A natural join is just a shortcut to avoid typing, with a presumption that the join is simple and matches fields of the same name.

SELECT
  *
FROM
  table1
NATURAL JOIN
  table2
    -- implicitly uses `room_number` to join

Is the same as...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON table1.room_number = table2.room_number

What you can't do with the shortcut format, however, is more complex joins...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON (table1.room_number = table2.room_number)
    OR (table1.room_number IS NULL AND table2.room_number IS NULL)

Gourmet

@JonathanLeffler - In MySQL, certainly. – Gourmet

OK - interesting. I asked because the SQL standard does not seem to permit this (but extensions are always possible). – Lapham

Which DBMS allows this non-standard syntax: NATURAL JOIN ... USING ()? The standard is either a NATURAL JOIN b or a JOIN b USING (c) – Metrical

"just a shortcut to avoid typing" is a misstatement. It's most significant feature is that it doesn't result in duplicate columns. – Smash

...for example, the result of your query that uses natural join will have just one column named room_number, whereas your inner joins will have two columns named room_number. – Smash

This is wrong, SQL natural join has only one copy for each column name that is in both inputs. – Chaschase

Answer 4

SQL is not faithful to the relational model in many ways. The result of a SQL query is not a relation because it may have columns with duplicate names, 'anonymous' (unnamed) columns, duplicate rows, nulls, etc. SQL doesn't treat tables as relations because it relies on column ordering etc.

The idea behind NATURAL JOIN in SQL is to make it easier to be more faithful to the relational model. The result of the NATURAL JOIN of two tables will have columns de-duplicated by name, hence no anonymous columns. Similarly, UNION CORRESPONDING and EXCEPT CORRESPONDING are provided to address SQL's dependence on column ordering in the legacy UNION syntax.

However, as with all programming techniques it requires discipline to be useful. One requirement for a successful NATURAL JOIN is consistently named columns, because joins are implied on columns with the same names (it is a shame that the syntax for renaming columns in SQL is verbose but the side effect is to encourage discipline when naming columns in base tables and VIEWs :)

Note a SQL NATURAL JOIN is an equi-join**, however this is no bar to usefulness. Consider that if NATURAL JOIN was the only join type supported in SQL it would still be relationally complete.

While it is indeed true that any NATURAL JOIN may be written using INNER JOIN and projection (SELECT), it is also true that any INNER JOIN may be written using product (CROSS JOIN) and restriction (WHERE); further note that a NATURAL JOIN between tables with no column names in common will give the same result as CROSS JOIN. So if you are only interested in results that are relations (and why ever not?!) then NATURAL JOIN is the only join type you need. Sure, it is true that from a language design perspective shorthands such as INNER JOIN and CROSS JOIN have their value, but also consider that almost any SQL query can be written in 10 syntactically different, but semantically equivalent, ways and this is what makes SQL optimizers so very hard to develop.

Here are some example queries (using the usual parts and suppliers database) that are semantically equivalent:

SELECT *
  FROM S NATURAL JOIN SP;

-- Must disambiguate and 'project away' duplicate SNO attribute
SELECT S.SNO, SNAME, STATUS, CITY, PNO, QTY
  FROM S INNER JOIN SP 
          USING (SNO);                        

-- Alternative projection
SELECT S.*, PNO, QTY
  FROM S INNER JOIN SP 
          ON S.SNO = SP.SNO;

-- Same columns, different order == equivalent?!
SELECT SP.*, S.SNAME, S.STATUS, S.CITY
  FROM S INNER JOIN SP 
      ON S.SNO = SP.SNO;

-- 'Old school'
SELECT S.*, PNO, QTY
  FROM S, SP 
 WHERE S.SNO = SP.SNO;

---

** Relational natural join is not an equijoin, it is a projection of one. – philipxy

Smash

Relational natural join is not an equijoin, it is a projection of one. SQL natural join is an SQL equijoin (duplicates possible)--it's defined in terms of inner join using. – Chaschase

@philipxy: Thanks, I've made amendments. Please feel free to edit - this or any of my answers - for misstatements and misunderstandings. I'm still learning from you :) – Smash

Answer 5

A NATURAL join is just short syntax for a specific INNER join -- or "equi-join" -- and, once the syntax is unwrapped, both represent the same Relational Algebra operation. It's not a "different kind" of join, as with the case of OUTER (LEFT/RIGHT) or CROSS joins.

See the equi-join section on Wikipedia:

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their use. The danger comes from inadvertently adding a new column, named the same as another column ...

That is, all NATURAL joins may be written as INNER joins (but the converse is not true). To do so, just create the predicate explicitly -- e.g. USING or ON -- and, as Jonathan Leffler pointed out, select the desired result-set columns to avoid "duplicates" if desired.

Happy coding.

---

(The NATURAL keyword can also be applied to LEFT and RIGHT joins, and the same applies. A NATURAL LEFT/RIGHT join is just a short syntax for a specific LEFT/RIGHT join.)

Shelbyshelden

"NATURAL join is just short syntax for [snipped] "equi-join" -- and, once the syntax is unwrapped, both represent the same Relational Algebra" - you are correct: that is true of the relational algebra but your answer breaks down after that e.g. "Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their use" - which experts in the relational algebra say that?! – Smash

"NATURAL join is just short syntax for [snipped] "equi-join" No, natural nner join is a projection of an (inner join of a form that can be reasonably called an) equijoin. Also there are both inner & outer natural joins. – Chaschase

Answer 6

Natural Join: It is combination or combined result of all the columns in the two tables. It will return all rows of the first table with respect to the second table.

Inner Join: This join will work unless if any of the column name shall be sxame in two tables

Astonish

I don't think your answer is clear enough and would take a large rewrite to fix it. – Smash

Answer 7

A Natural Join is where 2 tables are joined on the basis of all common columns.

common column : is a column which has same name in both tables + has compatible datatypes in both the tables. You can use only = operator

A Inner Join is where 2 tables are joined on the basis of common columns mentioned in the ON clause.

common column : is a column which has compatible datatypes in both the tables but need not have the same name. You can use only any comparision operator like =, <=, >=, <, >, <>

Heppman

Answer 8

Natural Join : A SQL Join clause combines fields from 2 or more tables in a relational database. A natural join is based on all columns in two tables that have the same name and selected rows from the two tables that have equal values in all matched columns.

--- The names and data types of both columns must be the same.

Using Clause : In a natural join,if the tables have columns with the same names but different data types, the join causes and error.To avoid this situation, the join clause can be modified with a USING clause. The USING clause specifies the columns that should be used for the join.

Shelley

This adds nothing to the many answers on this very old very voted very answered Q&A. Answers that add nothing should not be posted. How to Answer Help center Meta Stack Overflow Meta Stack Exchange PS This post says wrong things. Any natural join that fails from types will also fail if INNER JOIN USING is used instead. ON would be needed. – Chaschase

Answer 9

difference is that int the inner(equi/default)join and natural join that in the natuarl join common column win will be display in single time but inner/equi/default/simple join the common column will be display double time.

Allantoid

Answer 10

Inner join and natural join are almost same but there is a slight difference between them. The difference is in natural join no need to specify condition but in inner join condition is obligatory. If we do specify the condition in inner join , it resultant tables is like a cartesian product.

Faultfinding

Why is there no need to specify join conditions? Under what circumstances would specifying conditions in an inner join result in something like a cartesian product? – Smash

Calling outer and inner join "almost the same" is a slight understatement imho.. perhaps you can elaborate on your evaluation? – Opacity

Answer 11

mysql> SELECT  * FROM tb1 ;
+----+------+
| id | num  |
+----+------+
|  6 |   60 |
|  7 |   70 |
|  8 |   80 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
6 rows in set (0.00 sec)

mysql> SELECT  * FROM tb2 ;
+----+------+
| id | num  |
+----+------+
|  4 |   40 |
|  5 |   50 |
|  9 |   90 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
6 rows in set (0.00 sec)

INNER JOIN :

mysql> SELECT  * FROM tb1 JOIN tb2 ; 
+----+------+----+------+
| id | num  | id | num  |
+----+------+----+------+
|  6 |   60 |  4 |   40 |
|  7 |   70 |  4 |   40 |
|  8 |   80 |  4 |   40 |
|  1 |    1 |  4 |   40 |
|  2 |    2 |  4 |   40 |
|  3 |    3 |  4 |   40 |
|  6 |   60 |  5 |   50 |
|  7 |   70 |  5 |   50 |
|  8 |   80 |  5 |   50 |
.......more......
return 36 rows in set (0.01 sec) 
AND NATURAL JOIN :

    mysql> SELECT  * FROM tb1 NATURAL JOIN tb2 ;
    +----+------+
    | id | num  |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    +----+------+
    3 rows in set (0.01 sec)

Merrile

Answer 12

Inner join, join two table where column name is same.

Natural join, join two table where column name and data types are same.

Maida

This is completely incorrect. A NATURAL JOIN (as several people pointed out years ago) is one where the column names are the same. The data type need not be the same. The fields used for an INNER JOIN need not have the same name. – Foredeck