Published April 20, 2011 by

SQL- Sub Queries



A sub query is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand subqueries consider the following SELECT statement to retrieve the details of employees who belong to department 30.


SELECT * FROM EMPLOYEES


WHERE DEPARTMENT_ID=30;


In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.


If you notice this is a 2-step process involving:


1) Which department does Alexander Khoo belong to.


2) Who are the others who belong to the department number returned by the first step.


Subquery Syntax:


SELECT select_list


FROM table_name


WHERE column_name operator (SELECT select_list


FROM table_name


…)

Read More
    email this