# Correlated and Non-correlated Subquery

+ Non-correlated Subquery
  + Do NOT refer to tables or columns that are outside of the scope of the subquery

```
SELECT name, street, city, state FROM users WHERE userID IN (SELECT userID FROM allowlist);
```

+ Correlated Subquery

```
SELECT name, street, city, state FROM users 
WHERE EXISTS (SELECT 1 FROM allowlist WHERE allowlist.userID = users.userID);
```

------------------------------------------------------------------------------
A subquery is a query nested within another query and can be categorized into two types: non-correlated and correlated.

Non-correlated subqueries do not reference any tables or columns outside their own scope. For example:

```
SELECT name, street, city, state FROM users WHERE userID IN (SELECT userID FROM allowlist);
```

TiDB executes this query as follows:
1. It executes the subquery `SELECT userID FROM allowlist`.
2. It passes the subquery results to the outer query.

A correlated subquery, on the other hand, contains a reference to a table that also appears in the outer query. For example:

```
SELECT name, street, city, state FROM users WHERE EXISTS (SELECT * FROM allowlist WHERE allowlist.userID = users.userID);
```

This course primarily focuses on the optimization of correlated subqueries.