Subqueries

Nested Subqueries

The subquery is a SELECT statement nested within another command

SELECT.
SELECT column_1, column_2, ...
FROM table
WHERE column = (SELECT column
FROM table
WHERE condition);

The subquery is a SELECT procedure performed in the first place, so that his score was used for the outer query.

Subqueries that return a single row

Subqueries that return a single value can be treated as regular expression.

To find employees earning above the average, we can proceed as follows:

• Find the average salary:

SELECT AVG (SAL)
FROM EMP;

• Find employees whose salary is higher than the calculated average salary of the previous query.

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL > (result of a query from step 1)

These two commands can be combined into one:

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL > (SELECT AVG (SAL)
FROM EMP);

To find all employees in the same position at Smith, we write:

SELECT ENAME, JOB
FROM EMP
WHERE JOB = (SELECT JOB
FROM EMP
WHERE ENAME = 'Smith');

Subqueries that return multiple rows

If your organization would work more people named Smith, the previous subquery does not make sense. Moreover, the inner query would return a single value instead of a column value, which would lead to an error in the outer query.

Przekształćmy this query so that it searched all persons employed in positions that can run any SMITH.

SELECT ENAME, JOB
FROM EMP
JOB WHERE IN (SELECT JOB
FROM EMP
WHERE ENAME = 'Smith');

Let us now try to find the employees whose salary is listed as the highest salaries in the departments.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN (SELECT MAX (SAL)
FROM EMP
GROUP BY DEPTNO);

Now consider the situation that the company employs a person whose earnings are consistent with the most earnings in a given department, but she works in another department. The above query will write such a person, which was not imposed the condition that the person worked in the department, from which the highest salary.

The condition in which we compare multiple values

Let's try to rewrite the above query:

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (SAL, DEPTNO) IN (SELECT MAX (SAL) DEPTNO
FROM EMP
GROUP BY DEPTNO);

The above query will select us to people who earn the most in their departments - has been imposed as a condition that the person chosen to work in the department to which the highest salary.

The columns in the select list of internal (in the WHERE or HAVING clause) must be in the order and types consistent with the order and types appearing in the SELECT clause external command.