DB2 Subqueries

Code Example

## DB2 Subqueries

Subqueries are queries nested within another query.

### Types of Subqueries

1. **Scalar** - Returns single value
2. **Row** - Returns single row
3. **Table** - Returns multiple rows
4. **Correlated** - References outer query

### Subquery in WHERE
\`\`\`sql
SELECT * FROM EMP
WHERE SALARY > (SELECT AVG(SALARY) FROM EMP)
\`\`\`

### IN Subquery
\`\`\`sql
SELECT * FROM EMP
WHERE DEPT IN (SELECT DEPT FROM ACTIVE_DEPTS)
\`\`\`

### EXISTS Subquery
\`\`\`sql
SELECT * FROM DEPT D
WHERE EXISTS (SELECT 1 FROM EMP WHERE DEPT = D.DEPT)
\`\`\`

### Correlated Subquery
References outer query columns; executes once per outer row.

### Subquery in SELECT
\`\`\`sql
SELECT NAME,
       (SELECT DEPT_NAME FROM DEPT WHERE DEPT = E.DEPT) AS DEPT
FROM EMP E
\`\`\`