Clarity on concept of JOIN in SQL needed

Question

So the various type of joins we come across in SQL are :

1.JOIN

2.NATURAL JOIN

3.INNER JOIN

4.OUTER JOIN(LEFT, RIGHT, FULL)

5.CROSS JOIN

I need clarity in understanding what is the difference between JOIN, NATURAL JOIN and CROSS JOIN

At w3schools.com I used the JOIN and NATURAL JOIN query and got the following results

1.Query for JOIN.

SELECT *
FROM Orders
JOIN Customers;

Output of the JOIN query(Postal Code and Country Column can't be seen but they're there)

2.Query for NATURAL JOIN.

SELECT *
FROM Orders
NATURAL JOIN Customers;

Output of Natural Join

I'm a beginner and I'm not getting clear with concept of JOIN and NATURAL JOIN keyword since the material on internet is not sufficient or if it's there doesn't clear the confusion I have between these two keywords.

I can't understand why the number of records fetched by the 2 keywords are so different.Please explain in deep what's happening here.Thanks in advance.

You can read about it here https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join — Ferdinand Gaspar, Aug 21 '17 at 23:43
Forget about `NATURAL JOIN`. It is a blight on the language. Learn about inner joins, outer joins, and cross joins. — Gordon Linoff, Aug 21 '17 at 23:46
Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — jhpratt, Aug 22 '17 at 02:48

Ferdinand Gaspar · Accepted Answer · 2017-08-22T16:21:35.113

0

JOIN

SELECT *
  FROM orders o
  JOIN customers c
    ON o.customerid = c.customerid

Result

orderid, orderdate,  customerid, customerid, customername
10248,   1996-04-07, 1,          1,          Alfreds Futterkitse
10248,   1996-04-07, 2,          2,          Ana Trujillo
10248,   1996-04-07, 3,          3,          Antonio Moreno
10248,   1996-04-07, 4,          4,          Around the Horn
10248,   1996-04-07, 5,          5,          Berglunds snabbkop

The repeated column is avoided in NATURAL JOIN

NATURAL JOIN

 SELECT *
   FROM orders o
NATURAL JOIN customers c

Result

customerid, orderid, orderdate,  customername
1,          10248,   1996-04-07, Alfreds Futterkitse
2,          10248,   1996-04-07, Ana Trujillo
3,          10248,   1996-04-07, Antonio Moreno
4,          10248,   1996-04-07, Around the Horn
5,          10248,   1996-04-07, Berglunds snabbkop

Since you selected *, both customerid in orders and customers will display in JOIN but in NATURAL JOIN, it will only be one customerid column that will be displayed.

edited Aug 22 '17 at 16:21

answered Aug 22 '17 at 00:04

Ferdinand Gaspar

2,043
1
8
17

You say that but I don't see CustomerID column repeated in case of JOIN .Is it that they've not shown ? I know what you're saying is totally correct but the output is causing the confusion – Shubham Mishra Aug 22 '17 at 00:10
Also why there are only 196 records in case of Natural join but in case of Join there are like 178...(some number) records? – Shubham Mishra Aug 22 '17 at 00:10
Please see edited answer above with samples. With regards to your records, maybe it was affected by not using ON clause on your query for JOIN. – Ferdinand Gaspar Aug 22 '17 at 00:37

Clarity on concept of JOIN in SQL needed

1 Answers1