SQL_NATURAL_JOINS 1
Objective :

Compare natural join (IMPLICIT JOIN) and inner join (EXPLICIT JOIN CONDITION):

SCRIPT:

NATURAL JOIN: with no aliases:

SELECT ENAME, JOB , DEPTNO, LOC FROM EMP NATURAL JOIN DEPT WHERE DEPTNO = 30

NATURAL JOIN: With aliases:

SELECT e.ENAME, e.JOB , d.DEPTNO, d.LOC FROM EMP e NATURAL JOIN DEPT d WHERE DEPTNO = 30

 
ORACLE:

 

POSTGRESQL

WITH ALIASES:

MYSQL

WITH ALIASES:

Notes:

Compare natural join with inner join:

In natural join the joining-column can remain in query, as "deptno" common link to join, remained in the left-to-right querying ; the same is not true with inner join.

With old inner join and outer join, "deptno" , which is common in both, can't be used without aliasing like e.deptno . ( See this example : SQL_INNER_JOIN_ALIAS1.htm )

Runtime error :

 
select * FROM EMP inner join dept on emp.deptno = dept.deptno where emp.deptno =30

select e.ename,e.deptno,e.job, e.sal FROM EMP e inner join dept on dept.deptno = e.deptno where e.deptno =30

select e.ename,e.deptno,e.job, e.sal, d.loc FROM EMP e inner join dept d on d.deptno = e.deptno where e.deptno =30