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.

Answer 1

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.