-2

I'm really confused with this one. From what I learned, a natural join is an inner join that only works if table1 has some intercepting attributes with table2. Yet, when I try to play around with it, and say take a table that has no column names in common with another table, it acts as a normal cartessian product and displays the results as such.

In addition, when I try it again with different tables that have again nothing in common, it all of a sudden displays no results. I'm very confused, I think I'm missing something crucial here. Does anybody have an idea? Thanks!

JakeStud
  • 1
  • 2
  • 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

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,198,228
  • 53
  • 572
  • 709
  • Thank you! Now I get it. – JakeStud Jun 29 '20 at 23:32
  • 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