Join


Join Definition

Join keyword is used to get the data from one (self table) or multiple tables. To join n numbers of table, n-1 numbers of joins are required. Let’s try to understand different types of joins using DB_DEPT, DB_EMP AND DB_LOCATION tableS.

Type of Joins


INNER JOIN

joining of tables based on matching data(common values) in tables.

EQUI JOIN

We can retrieve data from multiple tables based on equality operator (i.e. '='operator). The joining columns must have same datatypes and joining will be performed by common values among the tables i.e. we can get matching rows only. We must specify alias name of the table to avoid ambiguity.

i. Join with using clause

If several columns have the same names but not same data types.

Syntax:
SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (common_column(s));
SELECT DEPTNO,ENAME FROM DB_DEPT JOIN DB_EMP
USING(DEPT_ID);

ii. Using INNER JOIN with ON clause

Get the data for employee id,employee name,department id and department name.

SELECT * FROM DB_DEPT D INNER JOIN DB_EMP E ON D.DEPTNO = E.DEPTNO;
SELECT E.EMPNO EMPLOYEE_ID, E.ENAME EMPLOYEE_NAME, E.DEPTNO DEPARTMENT_NO ,D.DNAME DEPARTMENT_NAME 
FROM DB_DEPT D, DB_EMP E 
WHERE D.DEPTNO = E.DEPTNO;

NON EQUI JOIN

If we use any operator (>,<,<=,>=,BETWEEN,IN) other than equality operator(=).

To find LEVEL for each employee.

SELECT E.EMPNO ,E.ENAME,E.SAL,S.LEVEL
FROM DB_EMP E INNER JOIN DB_SAL S
ON E.SAL BETWEEN S.MIN_SAL AND S.MAX_SAL;

SELF JOIN

It is used to join the table itself.To achieve self joinin single table, we create two instance(copy) of same table and apply the logic to get the required data.

Fetch employee data along with their manager data.

SELECT * FROM DB_EMP E1,DB_EMP E2 WHERE E1.EMPNO=E2.MGR_NO;

OUTER JOIN

We can retrieve matched and unmatched data both from multiple table.

LEFT OUTER JOIN

All the rows from left table and only matched rows from right table and if matching data is not available in right table then it will return null value.

SELECT * FROM DB_EMP E LEFT OUTER JOIN DB_DEPT D ON D.DEPTNO = E.DEPTNO

RIGHT OUTER JOIN

All the rows from right table and only matched rows from left table and if matching data is not available in left table then it will return null value.

SELECT * FROM DB_EMP E RIGHT OUTER JOIN DB_DEPT D ON D.DEPTNO = E.DEPTNO

FULL OUTER JOIN

All the rows from right table and lefttable and if matching data is not available then it will return null value.

SELECT * FROM DB_EMP E FULL OUTER JOIN DB_DEPT D ON D.DEPTNO = E.DEPTNO

CROSS JOIN

Each row of 1st table will be multiplied by all the rows in 2nd table. This join is also knows as Cartesian product of rows of tables. This join is rarely used.

Implicit CROSS JOIN

We do not use CROSS JOIN keyword.

Select * from DB_DEPT, DB_EMP;

Explicit CROSS JOIN

We use CROSS JOIN keyword.

Select * from DB_DEPT CROSS JOIN DB_EMP;

How to join more than 2 tables

Fetch employee details along with their department name and department location

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, L.LOCNAME
FROM DB_EMP E INNER JOIN DB_DEPT D
ON D.DEPTNO=E.DEPTNO
INNER JOIN DB_LOCATION L
WHERE L.LOCNO=101;

Footer with Map

Top