Union of two columns in the same table

advertisements

Say I have the following two columns in the same table

Column 1
--------
1
2
3

Column 2
--------
4
5
6

How do I get a result which gives me:

Columns
--------
1
2
3
4
5
6

Edit

What I'm really looking for is to make sure there is not a more efficient way of typing a union query on multiple columns in the same table without having to repeat which table it is multiple times and repeating a where condition for each union multiple times.

The actual query looks more like this:

WITH T1 AS
( SELECT [Col1] FROM [Table1]
)
SELECT * FROM (
    SELECT [Cols1-100], COUNT(*) as "Count" FROM (

        SELECT [Col-1] FROM [Table2] AS [Cols1-100], [T1]
        WHERE [Table2].[Col-1] = [T1].[Col-1]
        AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
        UNION ALL

        SELECT [Col-2] FROM [Table2] AS [Cols1-100], [T1]
        WHERE [Table2].[Col-1] = [T1].[Col-1]
        AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
        UNION ALL

        ....................... etc
        ....................... etc
        ....................... etc
        .... etc

        SELECT [Col-100] FROM [Table2] AS [Cols1-100], [T1]
        WHERE [Table2].[Col-1] = [T1].[Col-1]
        AND [Col-1] != '2' AND [Col-2] != '2' ...... etc .... AND [Col-100] != '2'

    ) as [Temp1]
    GROUP BY [Cols1-100]
) as [Temp2]

Using @Bohemian Outer Query I can do the following but testing the two query's, it seems a lot slower.

WITH T1 AS
( SELECT [Col1] FROM [Table1]
)
SELECT * FROM (
    SELECT [Cols1-100], COUNT(*) as "Count" FROM (
            SELECT * FROM (
                SELECT [Col-1] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2]
                UNION ALL
                SELECT [Col-2] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2]
                UNION ALL
                ....................... etc
                .... etc
                SELECT [Col-100] AS [Cols1-100], [Col-1], [Col-2], ..... etc ..... [Col-100] FROM [Table2]
            ) AS SUBQUERY WHERE [Col-1] IN (SELECT [Col1] FROM [T1])
            AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
        ) as [Temp1]
    GROUP BY [Cols1-100]
) as [Temp2]


select column1 as columns from mytable
union
select column2 from mytable

Using union removes duplicates (and on some databases also sorts).
If you want to preserve duplicates, use union all:

select column1 as columns from mytable
union all
select column2 from mytable

Edit:

To add a where clause, the easy but inefficient execution way is to add it as an outer query:

select * from (
    select column1 as columns from mytable
    union
    select column2 from mytable ) x
where columns ...

The more efficient execution way, but a painfully long query, is to put it on each subquery:

select column1 as columns from mytable
where ....
union
select column2 from mytable
where ...