Unions

Unions

To combine the results from multiple SELECT queries into a single result you use UNION like this:

$table2 = db_select('table2', 't2')
->fields('t2', array('column3', 'column4'));
$query = db_select('table1', 't1')
->fields('t1', array('column1', 'column2'))
->union($table2);

To order the combined result the individual SELECT queries must be parenthesized which currently is not supported (see Drupal's issue #1145076: UNION queries don't support ORDER BY clauses). A workaround is to put the UNIONed queries into a subquery and applying the ORDER BY clause to the combined result:

$table1 = db_select('table1', 't1')
->fields('t1', array('column1', 'column2'));
$table2 = db_select('table2', 't2')
->fields('t2', array('column3', 'column4'));
$query = Database::getConnection()
->select($table1->union($table2))
->fields(NULL, array('column1', 'column2'))
->orderBy('column1')
->orderBy('column2');