6

I'm studying SQL for a database exam and the way I've seen SQL is they way it looks on this page:

http://en.wikipedia.org/wiki/Star_schema IE join written the way Join <table name> On <table attribute> and then the join condition for the selection. My course book and my exercises given to me from the academic institution however, use only natural join in their examples. So when is it right to use natural join? Should natural join be used if the query can also be written using JOIN .. ON ?

Thanks for any answer or comment

The Unfun Cat
  • 29,987
  • 31
  • 114
  • 156
Niklas Rosencrantz
  • 25,640
  • 75
  • 229
  • 424
  • 4
    a very good visually Representative article on SQL joins might help you http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html – Sanath May 09 '12 at 06:47
  • 1
    http://en.wikipedia.org/wiki/Join_%28SQL%29 – Sanath May 09 '12 at 06:48
  • 1
    A [natural join](http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join) is a type of equi-join, meaning conditions use only equality (`=`) comparisons. If you had something like `... JOIN tblA a ON a.amount > b.amount`, that could not be expressed as a natural join. – Wiseguy May 09 '12 at 06:52
  • Furthermore, a natural join will join on _all_ columns it can. If you don't want to do that and you need to specify the column(s) you do want to join on, don't use a natural join. – Wiseguy May 09 '12 at 07:02

5 Answers5

10

A natural join will find columns with the same name in both tables and add one column in the result for each pair found. The inner join lets you specify the comparison you want to make using any column.

Saulo Vallory
  • 959
  • 9
  • 32
7

IMO, the JOIN ON syntax is much more readable and maintainable than the natural join syntax. Natural joins is a leftover of some old standards, and I try to avoid it like the plague.

baldy
  • 5,524
  • 4
  • 22
  • 19
  • 3
    natural joins have a lot of problems besides readability. E.g. if you change one of the tables you may break the query. – Saulo Vallory May 09 '12 at 16:37
  • using natural join your query will follow more what relational algebra demands. such a pity SQL doesn't follow closely the math that created it: https://en.wikipedia.org/wiki/Relational_algebra – Sombriks Jun 13 '16 at 14:38
2

A natural join will find columns with the same name in both tables and add one column in the result for each pair found. The inner join lets you specify the comparison you want to make using any column.

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Different Joins

*  JOIN: Return rows when there is at least one match in both tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables

INNER JOIN http://www.w3schools.com/sql/sql_join_inner.asp

FULL JOIN http://www.w3schools.com/sql/sql_join_full.asp

Okky
  • 10,338
  • 15
  • 75
  • 122
1

A natural join is said to be an abomination because it does not allow qualifying key columns, which makes it confusing. Because you never know which "common" columns are being used to join two tables simply by looking at the sql statement.

Helen Cui
  • 181
  • 2
  • 11
0

A NATURAL JOIN matches on any shared column names between the tables, whereas an INNER JOIN only matches on the given ON condition.

The joins often interchangeable and usually produce the same results. However, there are some important considerations to make:

  • If a NATURAL JOIN finds no matching columns, it returns the cross product. This could produce disastrous results if the schema is modified. On the other hand, an INNER JOIN will return a 'column does not exist' error. This is much more fault tolerant.

  • An INNER JOIN self-documents with its ON clause, resulting in a clearer query that describes the table schema to the reader.

  • An INNER JOIN results in a maintainable and reusable query in which the column names can be swapped in and out with changes in the use case or table schema.

  • The programmer can notice column name mis-matches (e.g. item_ID vs itemID) sooner if they are forced to define the ON predicate.

Otherwise, a NATURAL JOIN is still a good choice for a quick, ad-hoc query.

Gamma032
  • 441
  • 4
  • 7