-3

A natural join is an inner join that only works if table1 has some intersecting attributes with table2.

Yet, when I take tables that have no column names in common, it acts as a Cartesian product.

In addition, when I take different tables that have nothing in common, it displays no results.

Why?

philipxy
  • 14,867
  • 6
  • 39
  • 83
JakeStud
  • 21
  • 5
  • Right now you are just asking for us to rewrite your textbook & you have shown no research or other effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Quote the definitions you are relying on. PS There are many RAs (relational algebras). They differ in operators & even what a relation is. Give operator definitions & your reference for yours. Eg textbook name, edition & page. PS SQL & RA are different things. PS Give as much of a [mre] as you can. Which, depending on your RA, can include running code, google 'run relational algebra online'. – philipxy Jun 29 '20 at 23:50
  • Any specific question you have & how natural join works in general are faqs. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jun 29 '20 at 23:57
  • Does this answer your question? [Understanding natural join in Relational algebra](https://stackoverflow.com/questions/52171998/understanding-natural-join-in-relational-algebra) – philipxy Sep 29 '22 at 23:58

1 Answers1

1

Well, you have learned the first important lesson, which is to avoid natural join. It is just lousy syntax, because it does not even take properly declared foreign key relationships into account and the join conditions are hidden -- which makes queries hard to maintain and debug.

A natural join is an inner join equijoin with the join conditions on columns with the same names. Natural joins do not even take types into account, so the query can have type conversion errors if your data is really messed.

If the corresponding inner join on the common column names have no matches, then it returns the empty set. If there are no common column names, then it is the same as a cross join.

The way to think about it is that a natural join (inner natural join) generates the Cartesian product of two tables. When the tables have duplicated column names, then the final result set contains only those Cartesian-product rows where the common column names have the same value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    @JakeStud [Inner Join vs Natural Join vs USING clause: are there any advantages?](https://stackoverflow.com/a/35034568/3404097) PS RA natural join is not a special case of equijoin & SQL natural join is not a special case of inner join. SQL cross join is not a Cartesian product, it's just (wrongly) called that. PS This answer is about SQL. – philipxy Jun 29 '20 at 23:56
  • @Gordon `inner join` doesn't "take properly declared foreign key relationships into account" either. Why expect foreign keys to play a part in one operation but not in another? `inner join` also behaves the same w.r.t. types/conversion errors if you join `on` columns of different type. Your last para omits to mention an important difference between `natural join` vs Cartesian-product (as @philipxy points out). The important lesson @JakeStud should take away is that a lot of SQL so-called experts don't understand `natural join`, so spread misinformation, Fear, Uncertainty and Doubt about it. – AntC Jun 30 '20 at 09:28