CODEWITHSUNDEEP
sqlsql-serverdatabase-design
user3807468
user3807468

Reputation: 19

Natural Join Explanation

I am a student trying to learn Microsoft SQL and I am frustrated that I cannot understand how natural join works. I have a problem with a solution but I cannot understand how this solution was created through natural join. My friend sent my his old solution but cant remember how he got the result. I really want to figure out how this natural join works. Can someone please explain how this answer was achieved?

The question taken from "Database Design, Application, and Administration 6th Ed.:

  1. Show the result of a natural join that combines the Customer and OrderTbl tables.

Tables

Solution

Edit: The book doesn't give a query statement on how to get the results for the natural join in the chapters I have read so far. Which makes things much more confusing for me as I cannot simply add the table and send a query.

Edit2: The reason why some entries in the order table have null values is because the question is implying that some orders were processed through the internet. A friend of mine told me that was the reason why he got the question right opposed to some people who argued against it. :S

Upvotes: 0

Views: 2390

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

If I read your question correctly, you are trying to learn two concepts at the same time. The first is INNER JOIN (sometimes called equijoin). The second is natural join.

It's easier to explain natural join, assuming you already know how INNER JOIN works. A natural join is simply an equijoin where the column names indicate to you what the join condition ought to be. In your case, the fact that CustNo appears in both tables is the only clue you need in order to devise the correct join condition. You also include the join field only once in the result.

Column names are actually quite arbitrary, and could have been made very different in this case. for example, if the column Customer.CustNo had been named Customer.ID instead, you wouldn't be able to do a natural join.

for a correct solution in your case, see the answer provided by JamieC.

Upvotes: 1

Jamiec
Jamiec

Reputation: 136124

If you simply want the query which will result in the final table in your question here it is,

SELECT
 o.OrdNo,
 o.OrdDate,
 o.EmpNo,
 o.CustNo,
 c.CustFirstName
 c.CustLastName,
 c.CustCity,
 c.CustSatate,
 c.CustZip,
 c.CustBal
FROM OrderTbl o
  INNER JOIN Customer c
  ON o.CustNo = c.CustNo
ORDER BY c.CustNo

So, by way of explanation; this query selects all data from Customer and OrderTbl joining the two using CustNo which is the primary key (presumably) in Customer and a foreign key in OrderTbl. The ordering of the result is a little more tricky, and based almost purely on guesswork, I suspect the result is ordered by CustNo as well.

The Employee table does not feature at all in the result, however as the OrderTbl table has some blanks for EmpNo, you would almost certainly want a LEFT JOIN/RIGHT JOIN (as appropriate) if you wanted to retrieve any information about the employee from the orders table.

Upvotes: 1

Iain
Iain

Reputation: 387

MS SQL Server doesn't support NATURAL JOIN. However, if you were using a platform that would support it, a simple:

SELECT * FROM Customer NATURAL JOIN OrderTbl;

should do the trick.

https://en.wikipedia.org/wiki/Join_(SQL)#Natural_join is quite good.

Upvotes: 0

Related Questions