Joins in Oracle

Joins:-

Scenario:-
 

Suppose, there is a shop owner and he maintain his data in a table format. He has an order table and quantity table. His order table contains fields like order number, order date and quantity table contains order number, quantity. Now suppose that shop keeper want to see, against which order number how much quantity have been purchased. Shop keeper doesn’t have all the information in one table. If he wants to find the information he must fetch data from two table’s i.e.  Order table and quantity table. In order to fetch this data, shop keeper can join these tables and get the desired result. Join can be said as helper to the shop keeper which will help shop keeper to fetch data from two tables as per his requirement.
 

What join is?

Join is a query which allows to fetch data from two or more tables, views and materialized views. It combines data from two tables. Join can be used in a FROM clause or WHERE clause of the query. There are different types of joins available in oracle. And they are listed below:-
 

1)    Equijoin

2)    Self join

3)    Outer join

A.    Left outer join

B.   Right outer join

C.   Full outer join

4)    Cross join

Let’s discuss all the joins in detail:-

·         Equijoin

In simple words, equijoin is a join statement that uses (=) sign in the where condition. It will fetch data which is matched with the condition specified in where clause, like (a.deptno=b.deptno).

Syntax of equijoin is:-

SELECT A.COLUMN_NAME, B.COLUMN_NAME FROM TABLE_NAME A, TABLE_NAME B WHERE A.COLUMN_NAME=B.COLUMN_NAME;

Let’s have an example of an equijoin:-

SELECT A.EMPNO, A.EMPNAME, B.DEPTNO, B.DEPTTYPE FROM EMPLOYEE A, DEPARTMENT B WHERE A.DEPTNO=B.DEPTNO;

In above screen shot, I have selected two columns from employee table and two columns from department table on the condition that where both the tables have same department table.
 

·         Self join:-

As you can understand by its name, self join is a join which joins a table to itself.

Syntax of self join is:-

SELECT A.COLUMN_NAME B.COLUMN_NAME FROM A.TABLE_1 A, TABLE_1 B WHERE A.COLUMN_NAME=B._COLUMN_NAME;

Example is:-

         Outer join:-
 

Outer join is a kind of equijoin but we can fetch non-matching data from the tables. There are three outer join. Right outer join, left outer join and full outer join.
 

A)    Right outer join:-

Right outer join retrieves all the records from right side of the table in a select query and only matching records from the left side of the table in a select query.

Syntax of the right outer join is:-

SELECT A.COLUMN_LIST, B.COLUMN_LIST FROM TABLE A, TABLE B WHERE

 A.COLUMN_NAME (+) =B.COLUNN_NAME;

(+) Sign can be used instead of specifying RIGHT OUTER JOIN keyword.

Let’s have an example of right outer join:-

SELECT A.EMPNO, A.EMPNAME, B.DEPTNO

FROM EMPLOYEE A, DEPARTMENT B

WHERE A.DEPTNO (+) =B.DEPTNO;

We can specify (+) sign instead of specifying RIGHT OUTER JOIN keyword in the left side of the (=) sign.

Above query can be written as:-

SELECT A.EMPNO, A.EMPNAME, B.DEPTNO

FROM EMPLOYEE A RIGHT OUTER JOIN DEPARTMENT B

ON A.DEPTNO=B.DEPTNO;

 

B)   Left outer join

Left outer join retrieves all the records from left side of the table in a select query and only matching records from the right hand side of the table.

Syntax of the left outer join is:-

SELECT A.COLUMN_LIST, B.COLUMN_LIST FROM TABLE A, TABLE B

WHERE A.COLUMN_NAME=B.COLUMN_NAME (+);

Specify the (+) sign in the right side of the condition.

Let’s have an example:-

The above example can be written as

SELECT A.EMPNO, A.EMPNAME, B.DEPTNO

FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT B ON A.DEPTNO=B.DEPTNO;
 

C)   Full outer join:-

 

Full outer join retrieves all the records from both tables. Whenever there are no values, it will show NULL.

Syntax of full outer join is:-

SELECT A.COLUMN_LIST, B.COLUMN_LIST FROM TABLE A FULL OUTER JOIN TABLE B ON A.COLUMN_NAME =B.COLUMN_NAME;

Let’s have an example:-

SELECT * FROM EMPLOYEE;

SELECT * FROM DEPARTMENT;

SELECT A.EMPNO, A.EMPNAME, A.MGRID, B.DEPTTYPE

FROM EMPLOYEE A FULL OUTER JOIN DEPARTMENT B

ON A.DEPTNO=B.DEPTNO;

·         Cross join

Cross join is the join which fetch data multiplied times. Suppose you have 10 records in one table and 10 records in other table in select query then output would be 100 records.

Syntax of the cross join is:-

SELECT A.COLUMN_NAME_LIST, B.COLUMN_NAME_LIST FROM TABLE A, TABLE B;

Let’s have an example:-

SELECT A.EMPNO, A.EMPNAME, B.DEPTTYPE FROM EMPLOYEE A, DEPARTMENT B;

Above statement can be used as:-

SELECT A.EMPNO, A.EMPNAME, B.DEPTTYPE FROM EMPLOYEE CROSS JOIN DEPARTMENT;



Read Also:-