-3

Suppose I perform A natural join B, where:

A's schema is: (aID), where (aID) is a primary key.
B's schema is: (aID,bID), where (aID, bID) is a composite primary key.

Would performing the natural join work in this case? Or is it necessary for A to have both aID and bID for this to work?

philipxy
  • 14,416
  • 5
  • 32
  • 77
nikolas
  • 1
  • 1
  • 1
    What is a *definition* of natural join, and what does that tell you about this case? PS Please edit clarifications into your question, not comments. If your question is about relational algebra, use that tag, not sql. Did you even google 'relational algebra online'? http://dbis-uibk.github.io/relax/ – philipxy Oct 04 '17 at 06:02

3 Answers3

1

NATURAL JOIN returns rows with one copy each of the common input table column names and one copy each of the column names that are unique to an input table. It returns a table with all such rows that can be made by combining a row from each input table. That is regardless of how many common column names there are, including zero. When there are no common column names, that is a kind of CROSS JOIN aka CARTESIAN JOIN. When all the column names are common, that is a kind of INTERSECTION. All this is regardless of PKs, UNIQUE, FKs & other constraints.

NATURAL JOIN is important as a relational algebra operator. In SQL it can be used in a certain style of relational programming that is in a certain sense simpler than usual.

For a true relational result you would SELECT DISTINCT. Also relations have no special NULL value whereas SQL JOINs treat a NULL as not equal to a NULL; so if we treat NULL as just another value relationally then SQL will sometimes not return the true relational result. (When both arguments have a NULL for each of some shared columns and both have the same non-NULL value for each other shared column.)

philipxy
  • 14,416
  • 5
  • 32
  • 77
0

A "natural" join uses the names of columns to match between tables. It uses any matching names, regardless of key definitions.

Hence,

select . . .
from a natural join b

will use AId, because that is the only column with the same name.

In my opinion, natural join is an abomination. For one thing, it ignores explicitly declared foreign key relationships. These are the "natural join" keys, regardless of their names.

Second, the join keys are not clear in the SELECT statement. This makes debugging the query much more difficult.

Third, I cannot think of a SQL construct where adding a column or removing a column from a table takes a working query and changes the number of rows in the result set.

Further, I often have common columns on my tables -- CreatedAt, CreatedOn, CreatedBy. Just the existence of these columns precludes using natural joins.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
-1

Natural join will require both.

Regards,

Ted

Ted at ORCL.Pro
  • 1,550
  • 1
  • 6
  • 10
  • Thanks for the help, to continue off of that if in a new scenario where both relations had only 1 attribute which were both aID but one was a key and the other was not, would a natural join work in this case? – nikolas Oct 03 '17 at 23:06
  • Absolutely yes! – Ted at ORCL.Pro Oct 03 '17 at 23:12
  • So what is the reason for the initial example not working? I thought natural join just required atleast one common column to work. – nikolas Oct 03 '17 at 23:24
  • If I am not mistaken it requires columns to be the same name, I hope this clears things up! By the way... have heard of trial and error approach before? – Ted at ORCL.Pro Oct 03 '17 at 23:29
  • But A and B both share an attribute 'aID' doesn't that count as the common column? Or is it since B considers 'aID and bID' a primary key it doesn't work. Also I am learning relational algebra right now so I dont have an environment to test it. – nikolas Oct 03 '17 at 23:33
  • 3
    @nikolas This is wrong. Any two tables can be natural joined. It just returns all the rows with one copy each of the common input column names and one copy each of the column names that are unique to an input that can be made from a row from each input. That is regardless o how many common column names there are, including zero. And PKs, UNIQUE, FK &a other constraints are irrelevant. – philipxy Oct 04 '17 at 05:52
  • 2
    You don't need both columns to be in both tables. In fact you don't need any columns in common to do a natural join. Try it here: http://sqlfiddle.com/#!4/39ea2/2 – nvogel Oct 05 '17 at 13:23