lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter2Title

sqlIntroAdvancedChapter1Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- View the users table
SELECT * FROM users;

-- View the user_courses table
SELECT * FROM user_courses;

-- Return users who selected more than one course
SELECT name
FROM users
WHERE user_id IN (
  SELECT user_id
  FROM user_courses
  GROUP BY user_id
  HAVING COUNT(*) > 1
);

# Subquery in `WHERE` Clause

One of the most common uses of subqueries is inside the `WHERE` clause.

This allows you to filter results based on the output of another query.

<br/>

## Why It's Useful

Subqueries inside `WHERE` help you:
- Match dynamic values (like the top user, most common course, etc.)
- Filter results without hardcoding values
- Simplify queries without relying on extra joins

> You can think of it as: “Return a row **only if** it exists in another result.”

<br/>

## Example: Users who selected more than one course

The following query returns users who selected more than one course.

```sql title="Subquery in WHERE clause"
SELECT name
FROM users
WHERE user_id IN (
  SELECT user_id
  FROM user_courses
  GROUP BY user_id
  HAVING COUNT(*) > 1
);
```

### Result:

| name  |
| ----- |
| Sofia |
| Aisha |
| Liam  |

> These users picked **more than one** course.

<br/>

## Tips

* Always wrap subqueries in parentheses `()`
* Use `IN` or comparison operators (`=`, `>`, etc.)
* Watch out for performance on large datasets

Subqueries in the WHERE clause allow you to filter results by using the output of another query. This means you can dynamically match values, like users with more than one course, without hardcoding specific values. This approach leads to cleaner and more adaptable SQL queries.

Subquery in WHERE Clause

Why It's Useful

Example: Users who selected more than one course

Result:

Tips

What is a primary advantage of using subqueries in the WHERE clause of an SQL statement?

Subquery in `WHERE` Clause