Search code examples
mysqlsqlcross-join

Preferred way to cross join a table with (0,1)^T?

I have a table T1, and I want to get a new table with twice the rows: An extra column which gets 0 (for one copy of T1) and 1 (for a second copy of T1).

In other words, I want to cross-join T1 with a constant table with one Boolean column and all possible values.

What's the best/most efficient/more appropriate way to do this in MySQL?

Solution

  • If you want to do this with a cross join, you can do this:

    select t.*, const.which
from t cross join
     (select 0 as which union all select 1 as which) const

    This is definitely more efficient than UNION. Whether it is more efficient than UNION ALL depends on the database engine.

    If "t" is a subquery, then this is likely to be more efficient, since there is no chance that the subquery gets evaluated more than once.