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 #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');