lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter2Title

sqlIntroAdvancedChapter1Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- Count how many courses each user selected
SELECT u.name,
       (SELECT COUNT(*)
        FROM user_courses uc
        WHERE uc.user_id = u.user_id) AS course_count
FROM users u;

# Subqueries in SELECT

A subquery in the `SELECT` clause can add a calculated value to each row, such as a count, sum, or lookup.

It runs once for each row in the outer query and must return exactly one value (a scalar).

<br/>

## Syntax

Subqueries in the `SELECT` clause are written like this:

```sql title="Subquery in SELECT"
SELECT column1,
  (SELECT some_value FROM another_table WHERE ...) AS new_column
FROM main_table;
```

<br/>

## Example: Total Courses Per User

The following query returns the number of courses each user selected.

```sql title="Subquery in SELECT clause"
SELECT 
  u.name,
  (
    SELECT COUNT(*)
    FROM user_courses uc
    WHERE uc.user_id = u.user_id
  ) AS course_count
FROM users u;
```

### Output:

| name  | course_count |
| ----- | ------------- |
| Sofia | 2             |
| Ethan | 1             |
| Aisha | 2             |
| Noah  | 1             |
| Liam  | 3             |

> This query counts how many rows each user has in the `user_courses` table.

<br/>

## Why It's Useful

Use subqueries in `SELECT` when you want to:

* Add **extra calculated columns**
* Avoid joins for one-off metrics
* Keep reporting logic contained in a single query

<br/>

## Notes

* Make sure each subquery returns just one value (or SQL will error out)
* Useful for quick summaries, though joins are usually more efficient on large datasets
* You can use `COUNT`, `SUM`, `AVG`, or even nested subqueries

Subqueries in the SELECT clause are designed to return a single value, often referred to as a scalar value, for each row. This value could be a count, total, or lookup result. If a subquery returns multiple values, it will cause an error in SQL, as each subquery must resolve to one value per row.

### A subquery in the SELECT clause can return multiple values for each row.