Nesting SELECT statements

Some databases, like Microsoft SQL Server, allow nested SELECT statements, also known as sub-selects.

For example:

SELECT contacts.*, 
orders = ( SELECT COUNT(*) FROM orders WHERE orders.contact_id = contacts.id )
FROM contacts

This statement will select all contacts and show how many orders they have placed.

The COUNT(*) function is a so called Aggregate function. In this example COUNT(*) will count the number of records.