The SQL Joins clause is used to combine rows from two or more tables in a database. Joining tables must have at least one common column with same data type and same value.
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
Assume that we have the following tables.
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------------------------ --------- ----- --------- ----- ----- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 3450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 6000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 2300 10
14 rows selected.
EQUI JOIN
A join which contains an ‘=’ operator in the joins condition.
SQL> SELECT empno,ename,job,dname,loc
2 FROM emp e,dept d
3 WHERE e.deptno=d.deptno;
EMPNO ENAME JOB DNAME LOC
----- -------------- --------- -------------- ----------
7782 CLARK MANAGER ACCOUNTING NEW YORK
7839 KING PRESIDENT ACCOUNTING NEW YORK
7934 MILLER CLERK ACCOUNTING NEW YORK
7566 JONES MANAGER RESEARCH DALLAS
7902 FORD ANALYST RESEARCH DALLAS
7876 ADAMS CLERK RESEARCH DALLAS
7369 SMITH CLERK RESEARCH DALLAS
7788 SCOTT ANALYST RESEARCH DALLAS
7521 WARD SALESMAN SALES CHICAGO
7844 TURNER SALESMAN SALES CHICAGO
7499 ALLEN SALESMAN SALES CHICAGO
7900 JAMES CLERK SALES CHICAGO
7698 BLAKE MANAGER SALES CHICAGO
7654 MARTIN SALESMAN SALES CHICAGO
14 rows selected.
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
INNER JOIN
This will display all the records that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
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
Equi Joins
|
Equality operator =
in the join
|
Non Equi Joins
|
Other than = like <,>,Between, so on
|
Inner Join
|
Returns a row only when the columns in the join
contain values that satisfy the join condition. This
means that if a row has null value in one of the columns in the join
condition. That row isnt returned .
|
Outer join
|
Can return a row even when one of the column s in the
join condition contains a null value
|
Self join
|
Returns rows joined on the same tables
|
Natural join
|
All columns in the two
tables that have same name. It selects rows from the two tables that have
equal values in all matched column
|
Assume that we have the following tables.
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------------------------ --------- ----- --------- ----- ----- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 3450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 6000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 2300 10
14 rows selected.
EQUI JOIN
A join which contains an ‘=’ operator in the joins condition.
SQL> SELECT empno,ename,job,dname,loc
2 FROM emp e,dept d
3 WHERE e.deptno=d.deptno;
EMPNO ENAME JOB DNAME LOC
----- -------------- --------- -------------- ----------
7782 CLARK MANAGER ACCOUNTING NEW YORK
7839 KING PRESIDENT ACCOUNTING NEW YORK
7934 MILLER CLERK ACCOUNTING NEW YORK
7566 JONES MANAGER RESEARCH DALLAS
7902 FORD ANALYST RESEARCH DALLAS
7876 ADAMS CLERK RESEARCH DALLAS
7369 SMITH CLERK RESEARCH DALLAS
7788 SCOTT ANALYST RESEARCH DALLAS
7521 WARD SALESMAN SALES CHICAGO
7844 TURNER SALESMAN SALES CHICAGO
7499 ALLEN SALESMAN SALES CHICAGO
7900 JAMES CLERK SALES CHICAGO
7698 BLAKE MANAGER SALES CHICAGO
7654 MARTIN SALESMAN SALES CHICAGO
14 rows selected.
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
INNER JOIN
This will display all the records that have matched.
Ex:
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
No comments:
Post a Comment