Joins in SQL

SQL-Joins most important materials

The purpose of a join is to combine the data across tables.

A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

<!--[if !supportLists]-->

<!--[if !supportLists]-->

<!--[if !supportLists]-->

<!--[if !supportLists]-->

<!--[if !supportLists]-->

<!--[if !supportLists]-->

Left outer

Right outer

Full outer

<!--[if !supportLists]-->

<!--[if !supportLists]-->

<!--[if !supportLists]-->

Assume that we have the following tables.

SQL> select * from dept;

DEPTNO         DNAME          LOC

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

10                    mkt                 hyd

20                    fin                   bang

30                    hr                    bombay

SQL> select * from emp;

EMPNO ENAME         JOB                MGR DEPTNO

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

111     saketh            analyst           444     10

222     sudha             clerk               333     20

333     jagan              manager        111     10

444     madhu            engineer        222     40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex:

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

EMPNO ENAME JOB DNAME LOC

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

            111     saketh analyst mkt hyd

            333     jagan manager mkt hyd

            222     sudha clerk fin bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

EMPNO ENAME JOB DNAME LOC

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

            111     saketh analyst mkt hyd

            333     jagan manager mkt hyd

            222     sudha clerk fin bang

ON CLAUSE

SQL> select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

EMPNO ENAME JOB DNAME LOC

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

            111     saketh analyst mkt hyd

            333     jagan manager mkt hyd

            222     sudha clerk fin bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex:

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

EMPNO ENAME JOB DNAME LOC

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

222 sudha clerk mkt hyd

444 madhu engineer mkt hyd

444 madhu engineer fin bang

444 madhu engineer hr bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where

e1.empno=e2.mgr;

EMPNO ENAME JOB DEPTNO

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

111     jagan analyst 10

222     madhu clerk 40

333     sudha manager 20

444     saketh engineer 10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

SQL> select empno,ename,job,dname,loc from emp natural join dept;

EMPNO ENAME JOB DNAME LOC

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

111     saketh analyst mkt hyd

333     jagan manager mkt hyd

222     sudha clerk fin bang

CROSS JOIN

This will gives the cross product.

Ex:

SQL> select empno,ename,job,dname,loc from emp cross join dept;

EMPNO ENAME JOB DNAME LOC

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

111 saketh analyst mkt hyd

222 sudha clerk mkt hyd

333 jagan manager mkt hyd

444 madhu engineer mkt hyd

111 saketh analyst fin bang

222 sudha clerk fin bang

333 jagan manager fin bang

444 madhu engineer fin bang

111 saketh analyst hr bombay

222 sudha clerk hr bombay

333 jagan manager hr bombay

444 madhu engineer hr bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:

SQL> select empno,ename,job,dname,loc from emp e left outer join dept d

on(e.deptno=d.deptno);

Or

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);

EMPNO ENAME JOB DNAME LOC

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

111     saketh analyst mkt hyd

333     jagan manager mkt hyd

222     sudha clerk fin bang

444     madhu engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:

SQL> select empno,ename,job,dname,loc from emp e right outer join dept d

on(e.deptno=d.deptno);

Or

SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;

EMPNO ENAME JOB DNAME LOC

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

111     saketh analyst mkt hyd

333     jagan manager mkt hyd

222     sudha clerk fin bang

hr bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:

SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

on(e.deptno=d.deptno);

EMPNO ENAME JOB DNAME LOC

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

333 jagan manager mkt hyd

111 saketh analyst mkt hyd

222 sudha clerk fin bang

444 madhu engineer

hr bombay