What is Joins?
The type of Joins are using to extract data from multiple tables with single query.
Type of Joins?
There are two type of Joins.
|Oracle Joins||SQL Joins|
|Invalid Join||Cross Join|
|Equi Join||Natural Join|
|Non-Equi Join||Using Clause|
|Outer (+) Join||On Clause|
|Self Join||Left Outer Join|
|Right Outer Join|
|Full Outer Join|
In ORACLE JOINS syntax we never be using “join” keyword.
INVALID JOIN: Is a Cartesian product, invalid join select all row from first table and as well as select all rows from second table but in result it will be multiply by first table to second table for example, suppose in first table have 20 rows and in second table have 8 rows so invalid join return 20×8=160 rows.
NOTE: Avoid to use of Cartesian product, always use a valid join condition.
Invalid Join Example.
SQL> select employee_id,department_id,location_id from employees, departments;
EQUI JOIN: Equi Join work on Equal condition, check below select statement which is using Equi join to access the data from more then one table.
Equi join Example
SQL> select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;
As you can see in above query, we used equal condition in where clause, in both table have same name column and data matched in those columns, so which records are matched as per condition that will be showing in output.
NONEQUI JOIN: Non-equi join is used to return result from two or more tables, where exact join is not possible. For example we have emp table and salgrade table which is do not have any common column and in salgrade table have grade, losal and hisal column, and you want see the grade of employees based on employees salary then we are using NONEQUI JOIN.
Non-Equi Join Example
SQL> select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
OUTER JOIN (+): Outer join is work like left outer join and right outer join, if we are using (+) in left side then it’s called right outer join and if we are using right side then it’s called left outer join.
Outer Join Example
SQL> select e.ename, s.sal, d.dname, d.loc from emp e, dept d where e.deptno(+)=d.deptno;
SELF JOIN: Self join joining a table to itself. spouse you have a table name is EMP and you want to find all employees manager name, then you need to join EMP table to itself.
Self Join Example
SQL> select e.ename||' manager name is '||d.ename from emp e, emp d where e.mgr=d.empno;
Note: In SQL JOINS syntax always using “join” keyword.
CROSS JOIN: It’s same like invalid join.
Cross Join example
SQL> select ename, dname from emp cross join dept;
NATURAL JOIN: Natural join work based on common columns in between two tables. Which columns have same name and same datatypes. like in scott user have two tables EMP and DEPT, in both table have a deptno column common.
Natural Join Example
SQL> select ename, sal, dname, loc from emp natural join dept;
USING CLAUSE: Using clause use for join two and more tables but these tables must have a column common which is pacifying in using clause condition.
Using Clause Example
SQL> select ename, sal, job, dname, loc from emp join dept using(deptno);
ON CLAUSE: On clause is same like using clause but in on clause you have to specify which column are equivalent to this column, column name may be different but data and data type must be same.
On Clause Example
SQL> select e.ename, e.job, d.dname, d.loc from emp e join dept d on(e.deptno=d.deptno);
LEFT OUTER JOIN: Left Outer join is showing match and unmatched record from left side table.
Left Outer Join Example
SQL> select ename,dname from emp left outer join dept on(deptno);
RIGHT OUTER JOIN: Right outer join is showing match and unmatched records from right side table.
Right outer join Example
SQL> select ename, dname from emp right outer join dept on(deptno);
FULL OUTER JOIN: Full Outer Join is showing match and unmatched records from both tables.
Full outer join Example
SQL> select ename,dname from emp full outer join dept on(deptno);