LEFT JOIN vs INNER JOIN vs WHERE

=====================

General

=====================

LEFT JOIN vs INNER JOIN vs Regular WHERE

 

=====================

Example

=====================

Consider a table of EMP and DEPT where not all EMP entries have an entry in DEPT

How do you return all entries in EMP + Related entries for a specific entry in DEPT?

All there SQLs are the same.

INNER JOIN is the same as using WHERE

SELECT emp.*, dept.dname, dept.loc
  FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
       AND dept.dname = 'ACCOUNTING';

SELECT emp.*, dept.dname, dept.loc
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE dept.dname = 'ACCOUNTING';

SELECT emp.*, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.dname = 'ACCOUNTING';

empno ename  job  mgr   sal    deptno dname  loc

----- ------ ----------- ----- ------ ------ ----------- ----------

7839  KING   PRESIDENT          5000   10 ACCOUNTING  NEW YORK
7782  CLARK  MANAGER   7839    2450        10   ACCOUNTING    NEW YORK

LEFT JOIN is NOT the same as using WHERE
SELECT emp.*, dept.dname, dept.loc
FROM emp
LEFT JOIN dept
ON emp.deptno = dept.deptno
WHERE dept.dname = 'ACCOUNTING';

empno ename  job  mgr   sal    deptno dname  loc

----- ------ ----------- ----- ------ ------ ----------- ----------

7839  KING   PRESIDENT          5000   10 ACCOUNTING  NEW YORK
7782  CLARK  MANAGER   7839    2450        10   ACCOUNTING    NEW YORK

SELECT emp.*, dept.dname, dept.loc
FROM emp
LEFT JOIN dept
ON emp.deptno = dept.deptno
AND dept.dname = 'ACCOUNTING';

empno ename  job  mgr   sal    deptno  dname   loc

----- ------ ----------- ----- ------ ------  ----------- ----------

7839  KING   PRESIDENT          5000   10  ACCOUNTING  NEW YORK
7698  BLAKE  MANAGER   7839   2850     30

7782  CLARK  MANAGER   7839  2450     10  ACCOUNTING  NEW YORK

 566  JONES  MANAGER   7839  2975   20

7788  SCOTT  ANALYST      7566   3000     20

7902   FORD  ANALYST      7566   3000     20