Joins in oracle SQL ~ whereisstuff

Joins:

Ø SQL joins are used to combine rows from one or more tables based on the similar column between them.

Types of joins:

1. Inner join

2. Left outer join

3. Right outer join

4. Full outer join

Inner join

Ø It returns the intersecting data, which means matched values in the both tables.

Example:

SELECT *
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno

WHERE ename = 'KING' ;

Output:

Here Deptno column is repeating, so make query which is to be required fields.

Output:

Without where condition, the output will be,

SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,emp.deptno, dept.dname, dept.loc, emp.comm

FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno;

Output:

Left outer join:

Ø It returns all records from the left table and matched records from the right table.

Example:

SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,emp.deptno, dept.dname, dept.loc, emp.comm

FROM emp left outer JOIN dept
ON emp.deptno = dept.deptno;

Here,

Left table : EMP

Right table: DEPT

Output:

Right outer join:

Ø Right outer join is exactly opposite to the left outer join.

Ø It returns the all records from your right table and matched records from the left table.

Example:

SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,emp.deptno, dept.dname, dept.loc, emp.comm

FROM emp Right outer JOIN dept
ON emp.deptno = dept.deptno;

Here,

Right table: EMP

Left table : DEPT

Full outer join:

Ø Full outer is the combination of left and right outer joins.

Example:

SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,emp.deptno, dept.dname, dept.loc, emp.comm

FROM emp full outer JOIN dept
ON emp.deptno = dept.deptno;