Hypertechblog

What is co related sub query Co-related sub query  - if a sub query depends on output generated by outer query. Use-     Get Department details which is having no employee right now.    Get Product details which sold to any single Customer. Co-related sub query Uses two operator EXISTS- get object details   if having something then use EXISTS operator between Inner and outer query. NOT EXISTS- get object details   if not having something then use NOT   EXISTS  operator between Inner and outer query. Syntax - select ..... from table_name where EXISTS/NOT EXISTS ( select..... from table_name2 where table1.pkcolum = table2fkcolum  ); Example get department details where having at least 1 employee select d.* from dept d where EXISTS (select e.empno from emp e where e.deptno =  d.deptno); get department details where not having at least 1 employee select d.* from dept d where NOT EXISTS (select e.empno from emp e where e.deptno =  d.deptno);

What is sub query Sub query is a query with in other query is known is Sub query Use if you want to get data from table1 by using value form table2 we will go for subquery not for joins. syntax. select * from table_name where....(select * from table_name where...(select * from table_name)...)... // Inner most query execute first Type of Sub Query Single row sub query - the sub query which is returning only one output value. example- get department name of employee 708. select dname from department where deptno = (select deptno from employee where empno=708) example-  get department detailsof employee 708. select * from department where deptno = (select deptno from employee where empno=708) Multi row sub query - the sub query which is returning more than one output value. example-  get department detailsof employee 708.709 select dname from department where dept_no IN (select deptno from employee where empno IN( 708, 709)); If we want