Using Subqueries to Solve Queries in Oracle & Sql server 2008
Using Sub queries to Solve Queries
After completing this lesson, you should be able to do the following:
• Define subqueries
• Describe the types of problems that subqueries can solve
• List the types of subqueries
• Write single-row and multiple-row subqueries
SELECT select_list
FROM table
WHERE expirations operator (SELECT select_list FROM table);
SELECT last_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'SAM');
• Enclose subqueries in parentheses.
• Place subqueries on the right side of the comparison condition.
• The ORDER BY clause in the subquery is not needed unless you are performing Top-N analysis.
• Use single-row operators with single-row subqueries, and use multiple-row operators with
multiple-row subqueries.
multiple-row subqueries.
Single-Row Subqueries
Operator | Meaning |
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
<> | Not equal to |
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
Group Functions
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id =50);
Multiple-Row Subqueries
Operator | Meaning |
IN | Equal to any member in the list |
ANY | Compare value to each value returned by the subquery |
ALL | Compare value to every value returned by the subquery |
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <>'IT_PROG';
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Post a Comment