Interview Answer

Correctness is clearly the prime quality. If a system does not do what it is supposed to do, then everything else about it matters little. Bertrand Meyer

Explain the join? Different types of joins

January 1, 1970 12:00 am

Comments



Explain the join? Different types of joins


The employee table contains the following fields

													
													Name           Null     Type         
													-------------- -------- ------------ 
													EMPLOYEE_ID    NOT NULL NUMBER(6)    
													FIRST_NAME              VARCHAR2(20) 
													LAST_NAME      NOT NULL VARCHAR2(25) 
													EMAIL          NOT NULL VARCHAR2(25) 
													PHONE_NUMBER            VARCHAR2(20) 
													HIRE_DATE      NOT NULL DATE         
													JOB_ID         NOT NULL VARCHAR2(10) 
													SALARY                  NUMBER(8,2)  
													COMMISSION_PCT          NUMBER(2,2)  
													MANAGER_ID              NUMBER(6)    
													DEPARTMENT_ID           NUMBER(4)   
													
desc HR.departments;

Name            Null     Type         
--------------- -------- ------------ 
DEPARTMENT_ID   NOT NULL NUMBER(4)    
DEPARTMENT_NAME NOT NULL VARCHAR2(30) 
MANAGER_ID               NUMBER(6)    
LOCATION_ID              NUMBER(4)  

Inner Join or simple join

An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns. The simple join or EQUI JOIN fetch the data when two tables have the sample set of data. When the data stores in multiple table, it provides the scalability, more efficient storage. The data should not be duplicated and can be stores in multiple tables.

Natural Join

The Natural Join shows the table from two or more table without any duplciate data.

NON-EQUI JOIN

A join which contains an operator other than equal to '=' in the joins condition.

SELF JOIN

Joining the table itself is called self join.


Solution

   					 
-- Inner Join or simple join
select employee_id, first_name, last_name, email, department_name 
    from  HR.employees e join HR.departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID;
								

-- Multiple joins
select employee_id, first_name, last_name, email, department_name, job_title
    from  HR.employees e 
    join  HR.departments d on e.DEPARTMENT_ID=d.DEPARTMENT_ID
    join  HR.JOBS j on e.JOB_ID = j.JOB_ID;

-- Natural Join 
SELECT * FROM HR.employees NATURAL JOIN HR.departments;

-- NON-EQUI JOIN
select * from HR.employees e, HR.departments d 
	where e.DEPARTMENT_ID > d.DEPARTMENT_ID;

-- SELF JOIN
select e2.FIRST_NAME, e2.EMPLOYEE_ID, e2.MANAGER_ID 
	from HR.employees e1,HR.employees e2 where e1.EMPLOYEE_ID=e2.MANAGER_ID;

																	    

Comments



Please login to add comments.