For Drupal 8
Entity query:
$query = \Drupal::entityTypeManager()->getStorage('entity_type')->getQuery();
$query->condition('field/property', [1, 2, 3], 'IN');
$ids = $query->execute();
Sql Query (select), essentially the same for other query types.
$query = \Drupal::database()->select('table', 't');
$query->condition('column', [1, 2, 3], 'IN');
...
For Drupal 7
See Berdir's answer.
For Drupal 6
You can do it like this:
$nids = array(1, 2, 3);
$placeholders = db_placeholders($nids);
$result = db_query("SELECT * FROM {node} WHERE nid IN ($placeholders)", $nids);
db_placeholders is needed in Drupal 6 which will creating a string that holds the placeholders needed for the array of values given. Drupal 7 handles all this internally like Berdir describes.