There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Select emp_name, Dept_name from (Employee_table left outer join Dept_table on Employee_table.Dept_id = Dept_table.Dept_id)
  • Interview Candidate
  • 9
  • 4702

Showing Answers 1 - 9 of 9 Answers

Jayakumar M

  • Nov 8th, 2005
 

select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno(+);

  Was this answer useful?  Yes

apiplani

  • Dec 12th, 2005
 

What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected coloumns of the right table. eg in this query which uses the key-word LEFT OUTER JOIN. syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase :

Employee_table.Dept_id(+) = Dept_table.Dept_id

  Was this answer useful?  Yes

kiran Bandari

  • Dec 22nd, 2005
 

SELECT EMP.ENAME,EMP.DEPTNO,DEPT.DEPTNO FROM EMP LEFT JOIN DEPTWHERE EMP.DEPTNO=DEPT.DEPTNO

  Was this answer useful?  Yes

SREENIVAS POONDRU

  • Jan 3rd, 2006
 

Yes.You can use outer join and get the results.

  Was this answer useful?  Yes

samiksc

  • Jan 19th, 2006
 

Use the NVL function

select empname, NVL(deptname, 'null') from emp left outer join dept on emp.deptno = dept.deptno; 

  Was this answer useful?  Yes

zero_cool

  • Mar 6th, 2006
 

select ename, dnumber from employee left outer join department; I think that would be enough without necessarily saying "where employee.dnumber = department.dnumber since we are using "LEFT OUTER JOIN" statement--zero cool--

  Was this answer useful?  Yes

Evghenii

  • Jan 19th, 2007
 

select A.ENAME as Employee, B.ENAME as Manager from EMP A, EMP B where A.MGR=B.EMPNO group by A.ENAME;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions