How do you use subqueries in a SELECT statement in SQL?

You use subqueries in a SELECT statement in SQL by nesting a SELECT statement within another SQL command.

Subqueries, also known as inner queries or nested queries, are used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. They can be used with SELECT, INSERT, UPDATE, and DELETE statements. Subqueries can return individual values or a list of records; depending on how they are written, they can modify data in different ways.

A subquery is executed once before the main query. The result of the subquery is passed on to the main query which can use this result to complete its execution.

Here's a basic example of a subquery in a SELECT statement:

```
SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);
```

In this example, the subquery (the part in brackets) is run first and returns a list of values. The main query then runs, using the results of the subquery to filter the data.

Subqueries can be nested inside a WHERE or HAVING clause. They can also be nested inside another subquery. A subquery can contain another subquery. However, the level of subquery nesting in the FROM clause is not limited.

It's important to note that subqueries can often be rewritten as standard SELECT statements, or as joins, and this can improve performance. Subqueries in the SELECT statement are the most costly type of subquery. SQL Server must return the subquery results for every row in a transient table, so they can be a lot slower than an equivalent join or EXISTS operator.

Remember, when using subqueries in a SELECT statement in SQL, the subquery is run first and its results are used to complete the main query. This allows you to perform more complex data analysis and manipulation within your SQL commands.