How do you join a table to itself in sql?

-

 How do you join a table to itself in SQL?

You can join a table to itself by using the help of SELF JOIN concept.



SELF JOIN:


It is called as regular join. Each row in a table is joined to itself with every other row in the same table. Same table is referenced more than once will result into an 
.So,this concept come into the picture. To perform the self-join, left or inner join is used. It enables to join the rows with the same table as you thought both the tables are different. It is useful for eliminating the duplicate values. It is also useful for comparing the value in the hierarchical table.



Syntax:

select column_names
from table1 t1, table1 t2
WHERE condition;

In the above, t1 and t2 are the different table aliases for the same table table1.It will create virtual second instance for the table1 which can be referenced as the second table. In order to perform this join, you need to do different aliasing for the same table1 as t1 and t2. Otherwise, oracle engine will not distinguish the two different tables.

select t1.column1,t2.column2
FROM table1 AS t1
JOIN table2 AS t2
ON t1.common_column=t2.common_column;

Here, ON t1.common_column=t2.common_column is the join predicate comparing the common_column from the left table instance to the common_column from the right table instance.Here,ON is treated as clause to join the two tables on the basis of two column same values and having same datatype.

select * from emp;

It will display all the records present in the emp table.Now,self join is going to be performed on this table emp.

select e1.ename as EmployeeName1,e2.ename as EmployeeName2,e1.deptno as Deptno1,e2.deptno as Deptno2,e1.empno as Employeeno1,e2.empno as Employeeno2
from emp e1,emp e2
where e1.empno<>e2.empno
and e1.deptno=e2.deptno
order by e1.deptno;
When you execute the above query, below output or result will be displayed.


select e1.ename as EmployeeName1,e2.ename as EmployeeName2,e1.deptno as Deptno1,e2.deptno as Deptno2,e1.empno as Employeeno1,e2.empno as Employeeno2
from emp e1 inner join emp e2
ON e1.deptno=e2.deptno
order by e1.deptno;

When you execute the above query, below output or result will be displayed.

select e1.ename as EmployeeName1,e2.ename as EmployeeName2,e1.deptno as Deptno1,e2.deptno as Deptno2,e1.empno as Employeeno1,e2.empno as Employeeno2
from emp e1 LEFT join emp e2
ON e1.deptno=e2.deptno
order by e1.deptno;

When you execute the above query, below output or result will be displayed.

select e1.ename as EmployeeName1,e1.sal as Employee1sal1,e2.ename as EmployeeName2 ,e2.sal as Employee1sal2
from emp e1 join emp e2
ON e1.deptno=e2.deptno
AND e1.sal<e2.sal;

It will compare the employee's salary in the same department. It allows us to match the records of higher salaries with lower salaries of the employees in the same department number.


RECURSIVE SELF JOIN:

It is simply just extension which joins a table to itself continuously. It is worked in the nested hierarchies or relationships.

create table manager_employee(employee_id number,employee_name varchar2(20),manager_id number,manager_name varchar2(20));


INSERT ALL
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (100,'Rahul_Kumar',700,'Laxman_yadav')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (200,'Rohan_Singh',500,'Ramesh_Jha')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (300,'Shyam_Monhar',100,'Rahul_Kumar')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (400,'Mohan_Lal',200,'Rohan_Singh')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (500,'Ramesh_Jha',300,'Shyam_Monhar')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (600,'Sohan_prasad',400,'Mohan_Lal')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (700,'Laxman_yadav',600,'Sohan_prasad')
INTO manager_employee(employee_id,employee_name,manager_id,manager_name) VALUES (800,'Shyam_lal','','')
SELECT * FROM dual;

select * from manager_employee;



select e.employee_name AS employeename,m.manager_name AS managername
from manager_employee e
JOIN manager_employee m
ON e.employee_id=m.manager_id;

select e.employee_name AS employeename,m.manager_name AS managername
from manager_employee e
LEFT JOIN manager_employee m
ON e.employee_id=m.manager_id;



Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more