SQL natural join query not selecting expected results

Question

I'm trying to make a query with two natural joins, but I'm not getting the expected results. I want to retrieve the employers data who have an assigned project. In addition I want to retrieve the project code, the project name and the hours that the employee has spent on the project.

This is the SQL query I'm trying to make:

SELECT empno, ename, sal, prono, hours, pname
FROM emp NATURAL RIGHT JOIN emppro NATURAL JOIN pro;

This is the emp table:

EMPNO ENAME  JOB         MGR HIREDATE     SAL     COMM    DEPTNO
----- ---------- --------- ----- -------- ------- ------- ------
7369 SMITH   CLERK      7902 17/12/80     800             20
7499 ALLEN   SALESMAN   7698 20/02/81   1,600     300     30
7521 WARD    SALESMAN   7698 22/02/81   1,250     500     30
7566 JONES   MANAGER    7839 02/04/81   2,975             20
7654 MARTIN  SALESMAN   7698 28/09/81   1,250     1,400   30
7698 BLAKE   MANAGER    7839 01/05/81   2,850             30
7782 CLARK   MANAGER    7839 09/06/81   2,450             10
7788 SCOTT   ANALYST    7566 19/04/87   3,000             20
7839 KING    PRESIDENT   17/11/81       5,000             10
7844 TURNER  SALESMAN   7698 08/09/81   1,500      0      30
7876 ADAMS   CLERK      7788 23/05/87   1,100             20
7900 JAMES   CLERK      7698 03/12/81     950             30
7902 FORD    ANALYST    7566 03/12/81   3,000             20
7934 MILLER  CLERK      7782 23/01/82   1,300             10

This is the emppro table:

EMPNO      PRONO      HOURS
----- ---------- ----------
7499        1004     15
7499        1005     12
7521        1004     10
7521        1008      8
7654        1001     16
7654        1006     15
7654        1008      5
7844        1005      6
7934        1001      4

And this is the pro table:

PRONO       PNAME      LOC          DEPTNO
---------- ---------- ------------- ------
1001        P1         BOSTON         20
1004        P4         CHICAGO        30
1005        P5         CHICAGO        30
1006        P6         LOS ANGELES    30
1008        P8         NEW YORK       30

If I make the query with inner joins it works properly, why? I think I'm not understanding correctly the natural join...

Thanks.

Answer 1

No one uses NATURAL JOIN precisely for these reasons. Your query:

SELECT empno, ename, sal, prono, hours, pname
FROM emp NATURAL RIGHT JOIN emppro NATURAL JOIN pro;

Is equivalent to this query:

SELECT empno, ename, sal, prono, hours, pname
FROM emp 
RIGHT JOIN emppro ON emp.empno = emppro.empno
JOIN pro ON emppro.prono = pro.prono
  AND emp.deptno = pro.deptno -- This predicate is completely unexpected and wrong

As you can see above, you accidentally joined over the DEPTNO columns that happen to be present in both emp and pro, but you didn't want to join over that column.

Think about your original query this way:

SELECT empno, ename, sal, prono, hours, pname
FROM A NATURAL JOIN pro;

Where A = (emp NATURAL RIGHT JOIN emppro). So, when joining pro to A, there are two columns that A and pro have in common.

Answer 2

NATURAL JOIN is just an error waiting to happen (as is presumably happening in your case). It chooses the columns for joining based on the names in the associated tables/subqueries. It doesn't even use declared foreign key relationships. Seems like a real bad idea to me. Just forget that it exists.

So, use USING instead (or if you prefer ON). Something like this:

SELECT empno, ename, sal, prono, hours, pname
FROM emp RIGHT JOIN emppro
     emppro
     USING (EMPNO) INNER JOIN
     pro
     USING (PRONO);

I'm not a fan of RIGHT JOIN either. I think LEFT JOIN is easier to follow logically, particularly if you are learning SQL ("keep all the rows in the first table and matching rows in the second").