What is the difference between a natural join and an inner join?
This question is related to
sql
join
natural-join
difference is that int the inner(equi/default)join and natural join that in the natuarl join common column win will be display in single time but inner/equi/default/simple join the common column will be display double time.
Natural Join: It is combination or combined result of all the columns in the two tables. It will return all rows of the first table with respect to the second table.
Inner Join: This join will work unless if any of the column name shall be sxame in two tables
SQL is not faithful to the relational model in many ways. The result of a SQL query is not a relation because it may have columns with duplicate names, 'anonymous' (unnamed) columns, duplicate rows, nulls, etc. SQL doesn't treat tables as relations because it relies on column ordering etc.
The idea behind NATURAL JOIN
in SQL is to make it easier to be more faithful to the relational model. The result of the NATURAL JOIN
of two tables will have columns de-duplicated by name, hence no anonymous columns. Similarly, UNION CORRESPONDING
and EXCEPT CORRESPONDING
are provided to address SQL's dependence on column ordering in the legacy UNION
syntax.
However, as with all programming techniques it requires discipline to be useful. One requirement for a successful NATURAL JOIN
is consistently named columns, because joins are implied on columns with the same names (it is a shame that the syntax for renaming columns in SQL is verbose but the side effect is to encourage discipline when naming columns in base tables and VIEW
s :)
Note a SQL NATURAL JOIN
is an equi-join**, however this is no bar to usefulness. Consider that if NATURAL JOIN
was the only join type supported in SQL it would still be relationally complete.
While it is indeed true that any NATURAL JOIN
may be written using INNER JOIN
and projection (SELECT
), it is also true that any INNER JOIN
may be written using product (CROSS JOIN
) and restriction (WHERE
); further note that a NATURAL JOIN
between tables with no column names in common will give the same result as CROSS JOIN
. So if you are only interested in results that are relations (and why ever not?!) then NATURAL JOIN
is the only join type you need. Sure, it is true that from a language design perspective shorthands such as INNER JOIN
and CROSS JOIN
have their value, but also consider that almost any SQL query can be written in 10 syntactically different, but semantically equivalent, ways and this is what makes SQL optimizers so very hard to develop.
Here are some example queries (using the usual parts and suppliers database) that are semantically equivalent:
SELECT *
FROM S NATURAL JOIN SP;
-- Must disambiguate and 'project away' duplicate SNO attribute
SELECT S.SNO, SNAME, STATUS, CITY, PNO, QTY
FROM S INNER JOIN SP
USING (SNO);
-- Alternative projection
SELECT S.*, PNO, QTY
FROM S INNER JOIN SP
ON S.SNO = SP.SNO;
-- Same columns, different order == equivalent?!
SELECT SP.*, S.SNAME, S.STATUS, S.CITY
FROM S INNER JOIN SP
ON S.SNO = SP.SNO;
-- 'Old school'
SELECT S.*, PNO, QTY
FROM S, SP
WHERE S.SNO = SP.SNO;
** Relational natural join is not an equijoin, it is a projection of one. – philipxy
A Natural Join is where 2 tables are joined on the basis of all common columns.
common column : is a column which has same name in both tables + has compatible datatypes in both the tables. You can use only = operator
A Inner Join is where 2 tables are joined on the basis of common columns mentioned in the ON clause.
common column : is a column which has compatible datatypes in both the tables but need not have the same name.
You can use only any comparision operator like =
, <=
, >=
, <
, >
, <>
natural left
or natural right
) that assumes the join criteria to be where same-named columns in both table matchI would avoid using natural joins like the plague, because natural joins are:
Inner join, join two table where column name is same.
Natural join, join two table where column name and data types are same.
A NATURAL
join is just short syntax for a specific INNER
join -- or "equi-join" -- and, once the syntax is unwrapped, both represent the same Relational Algebra operation. It's not a "different kind" of join, as with the case of OUTER
(LEFT
/RIGHT
) or CROSS
joins.
See the equi-join section on Wikipedia:
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their use. The danger comes from inadvertently adding a new column, named the same as another column ...
That is, all NATURAL
joins may be written as INNER
joins (but the converse is not true). To do so, just create the predicate explicitly -- e.g. USING
or ON
-- and, as Jonathan Leffler pointed out, select the desired result-set columns to avoid "duplicates" if desired.
Happy coding.
(The NATURAL
keyword can also be applied to LEFT
and RIGHT
joins, and the same applies. A NATURAL LEFT/RIGHT
join is just a short syntax for a specific LEFT/RIGHT
join.)
mysql> SELECT * FROM tb1 ;
+----+------+
| id | num |
+----+------+
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tb2 ;
+----+------+
| id | num |
+----+------+
| 4 | 40 |
| 5 | 50 |
| 9 | 90 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
6 rows in set (0.00 sec)
INNER JOIN :
mysql> SELECT * FROM tb1 JOIN tb2 ;
+----+------+----+------+
| id | num | id | num |
+----+------+----+------+
| 6 | 60 | 4 | 40 |
| 7 | 70 | 4 | 40 |
| 8 | 80 | 4 | 40 |
| 1 | 1 | 4 | 40 |
| 2 | 2 | 4 | 40 |
| 3 | 3 | 4 | 40 |
| 6 | 60 | 5 | 50 |
| 7 | 70 | 5 | 50 |
| 8 | 80 | 5 | 50 |
.......more......
return 36 rows in set (0.01 sec)
AND NATURAL JOIN :
mysql> SELECT * FROM tb1 NATURAL JOIN tb2 ;
+----+------+
| id | num |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.01 sec)
A natural join is just a shortcut to avoid typing, with a presumption that the join is simple and matches fields of the same name.
SELECT
*
FROM
table1
NATURAL JOIN
table2
-- implicitly uses `room_number` to join
Is the same as...
SELECT
*
FROM
table1
INNER JOIN
table2
ON table1.room_number = table2.room_number
What you can't do with the shortcut format, however, is more complex joins...
SELECT
*
FROM
table1
INNER JOIN
table2
ON (table1.room_number = table2.room_number)
OR (table1.room_number IS NULL AND table2.room_number IS NULL)
Inner join and natural join are almost same but there is a slight difference between them. The difference is in natural join no need to specify condition but in inner join condition is obligatory. If we do specify the condition in inner join , it resultant tables is like a cartesian product.
Source: Stackoverflow.com