# Sub Queries

A sub-query is a **select query** that is contained inside another query. The inner select query is usually used to determine the results of the outer select query.

**Using Murach Database, find employees who work in Payroll Department.**

```
use Murach;

select 
    department_number
from 
    departments 
where 
    department_name = 'Payroll';

SELECT 
    * 
FROM 
    employees 
where 
    department_number = Value from previous query;
```

### Now using Subquery

```
select 
    * 
from 
    employees 
where 
    department_number = 
    (select 
        department_number 
    from 
        departments 
    where 
        department_name = 'Payroll'

   );
```

**Using Chinook database**, simple Subquery to find out Names of Support Representative

```
SELECT * FROM Employee;

SELECT * FROM Customer;

SELECT * FROM Employee WHERE EmployeeId IN (SELECT SupportRepId FROM Customer);
```

**Using Sakila database**, find the actors who acted in the movie Ace Goldfinger

```
use sakila;

SELECT * FROM actor
WHERE actor_id IN 
    (SELECT actor_id FROM film_actor
    WHERE film_id = 
        (SELECT film_id FROM film 
        WHERE title = 'Ace Goldfinger')
    );
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://gchandra.gitbook.io/data-warehousing/rdbms/sql-concepts/sub-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.