0

So for a fun little lab my Professor assigned, he wants us to create our own queries using different join operations. The ones that I'm curious about are NATURAL JOIN and JOIN ON.

The textbook definition of a natural join - "returns all rows with matching values in the matching columns and eliminates duplicates columns." So, say I have two tables, Customers and Orders. I list all orders submitted by the customer with an id = 1 as follows:

Select Customers.Name
From Customers, Orders
Where Customers.ID = 1
AND Customers.ID = Orders.CID

I want to know how that is different from JOIN ON, which according to the textbook "returns rows that meet the indicated join condition, and typically includes an equality comparison of two expressed columns" i.e. a primary key of one table and a foreign key of another. So a JOIN ON clause essentially does the same thing as a natural join. It returns all rows with matching values according to the parameters specified in the ON clause.

Select Customers.Name
From Customers JOIN Orders ON Customers.ID = Orders.CID

Same results. Is the latter just an easier way to write a natural join, or is there something I'm missing here?

Kinda like how in JavaScript, I can say:

var array = new Array(1, 2, 3);

OR I could just use the quicker and easier literal, without the constructor:

var array = [1, 2, 3];

Edit: Didn't even realize that the natural join uses a JOIN keyword in the FROM clause, and omits the WHERE clause. That just shows how little I know about this language. I'll keep the error for the sake of tracking my own progress.

Streamer
  • 173
  • 1
  • 1
  • 11
  • Possible duplicate of http://stackoverflow.com/questions/121631/inner-join-vs-where – KC Wong Nov 07 '16 at 04:31
  • So as suspected, they are the same... interesting! I guess I should just answer my own question here? – Streamer Nov 07 '16 at 04:42
  • Possible duplicate of [SQL join: where clause vs. on clause](http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) – NineBerry Nov 08 '16 at 10:59

1 Answers1

3

NATURAL JOIN is :

always an equi-join
always matches by equality of all of the same-named attributes

which in essence boils down to there being no way at all to specify the JOIN condition. So you can only specify T1 NATURAL JOIN T2 and that's it, SQL will derive the entire matching condition from just that.

JOIN ON is :

not always an equi-JOIN (you can also specify JOIN ON T1.A > T2.A)
not always involving all attributes that correspond by name (if both tables have an attribute named A, you can still leave out ON T1.A = T2.A).

Your ID/CID example is not suitable for using NATURAL JOIN directly. You would have to rename attributes to get the column/attribute matching you want by stating [something like] :

SELECT Customers.Name From Customers NATURAL JOIN (SELECT CID as ID FROM Orders)

(And as you stated in the question yourself, there is the thing about duplicate removal, which no other form of JOIN does by and of itself. It's an issue of scrutinous conformance to relational theory, which SQL as a whole doesn't exactly excel at, to put it mildly.)

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • The range variable `Customers` in `Customers.Name` is redundant and you probably want `SELECT DISTINCT` because `NATURAL JOIN` fails to remove the duplicate *rows* :) – onedaywhen Nov 09 '16 at 14:29
  • Ouch. Seems I missed the precise meaning of "duplicate removal" in the particular context of SQL natural join. Probably should have stayed with my original plan to refer to "SQL comparative survey" for all/most of the nitty gritty details. I've never encountered the JOIN ON form you mention. You're presumably right. – Erwin Smout Nov 09 '16 at 15:02
  • My bad! I meant to say, `JOIN USING ( )` will also exhibit duplicate attribute removal. – onedaywhen Nov 09 '16 at 16:27