Devry Goodness Essay

620 Words3 Pages
SubQueries 0 A subquery is an additional method for handling multi-table manipulations. It helps us to use multiple tables in a single SQL statement without using joins. 1 It is a query that is the inner query of a nested query and satisfies the requirements of outer query. 2 Operators like IN, ALL are used in a subquery. 3 There may sometimes be a slight performance advantage to using a join query rather than a subquery. However if we need data from more than one table we have to use a join query. On the other hand, using sub-queries may make the code more elegant and easier to read and it may seem easier to answer the question that way rather than using a join. Syntax: outer query(subquery); Note: The subquery is inside the outer (main) query. The subquery returns a value as a parameter for the outer query. So the outer query depends on the return value of the subquery. Query with Equi Join: SELECT e.EmpID, d.Deptno, LName FROM Dept d, Employees e WHERE d.Deptno = e.Deptno AND d.Deptno IN (’10’ , ’30’) ; Results: EMPID DEPTNO LNAME ----- ------ --------------- e22 10 Weber e23 30 Rasaq * The IN operator was earlier used to replace logical OR while retrieving records from a single table. The IN operator is also used to join tables in a subquery. * The IN operator is used to return the records in the outer query that meets the requirements of the criteria in the subquery. SELECT EmpID, DeptNo, LName FROM Employees WHERE DeptNo IN (SELECT DeptNo FROM Dept WHERE DeptNo = '10' OR DeptNo = '30' ); Results: EMPID DE LNAME ----- -- --------------- e22 10 Weber e23 30 Rasaq SELECT EmpID, DeptNo, LName FROM Employees WHERE DeptNo NOT IN (SELECT DeptNo FROM Dept WHERE DeptNo = '10‘ OR DeptNo = ‘30‘ ); Results: EMPID DEPTNO LNAME ----- ------

More about Devry Goodness Essay

Open Document