Wednesday, August 8, 2012

exists and not exists in sql server

The predicate EXISTS will return true when the sub query return at least one row and otherwise is returns false
NOT EXISTS will return true when the sub query return no  rows and otherwise is returns false
The below example display the details of emp to when at least one employee is reporting

select *from emp M where EXISTS(select *from emp E where E.no=M.empno)

Display the details of all departments in which there are no emps

select *from dept where not exists(select *from emp e where e.deptno=d.deptno)
Post a Comment

Bel