Unions  
Author Message
opstandzon





PostPosted: Mon Nov 29 07:55:08 CST 2004 Top

SQL Server Developer >> Unions

Hi!

I have two tables I want to union, tableA and tableB. Apart from a single
column 'common', the tables have different column names. I want to select
all columns from both tables, and I want columns from tableA to get NULL
values for tableB and vice versa.

Example:

tableA contents (comma-separated, with header):
columnA, common
'From A', 123

tableB contents (comma-separated, with header):
columnB, common
'From B', 234

I want a query which returns the following result set (ordering of the
columns is unimportant):

columnA, columnB, common
'From A', NULL, 123
NULL, 'From B', 234


I know I can accomplish this by using the following query:

SELECT columnA, NULL AS columnB, common FROM tableA
UNION ALL
SELECT NULL AS columnA, columnB, common FROM tableB


But in my real-world scenario, there are lots of columns and three tables
involved in the union. Therefore, I'm curious as to whether or not there are
any other methods of accomplish the same thing, one which doesn't involve
specifying all columns for each table?

Thanks!


Regards,
Nils Magnus ENglund

SQL Server230  
 
 
avnrao





PostPosted: Mon Nov 29 07:55:08 CST 2004 Top

SQL Server Developer >> Unions i think based on your requirement, you will be using union all and specify
alll columns explicitly.

but questioning the design, what is this common column? is it a foreign key
reference? or something else?
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet


> Hi!
>
> I have two tables I want to union, tableA and tableB. Apart from a single
> column 'common', the tables have different column names. I want to select
> all columns from both tables, and I want columns from tableA to get NULL
> values for tableB and vice versa.
>
> Example:
>
> tableA contents (comma-separated, with header):
> columnA, common
> 'From A', 123
>
> tableB contents (comma-separated, with header):
> columnB, common
> 'From B', 234
>
> I want a query which returns the following result set (ordering of the
> columns is unimportant):
>
> columnA, columnB, common
> 'From A', NULL, 123
> NULL, 'From B', 234
>
>
> I know I can accomplish this by using the following query:
>
> SELECT columnA, NULL AS columnB, common FROM tableA
> UNION ALL
> SELECT NULL AS columnA, columnB, common FROM tableB
>
>
> But in my real-world scenario, there are lots of columns and three tables
> involved in the union. Therefore, I'm curious as to whether or not there
are
> any other methods of accomplish the same thing, one which doesn't involve
> specifying all columns for each table?
>
> Thanks!
>
>
> Regards,
> Nils Magnus ENglund
>
>


 
 
Milind





PostPosted: Mon Nov 29 08:18:02 CST 2004 Top

SQL Server Developer >> Unions


> i think based on your requirement, you will be using union all and specify
> alll columns explicitly.
>
> but questioning the design, what is this common column? is it a foreign
key
> reference? or something else?
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet


> > Hi!
> >
> > I have two tables I want to union, tableA and tableB. Apart from a
single
> > column 'common', the tables have different column names. I want to
select
> > all columns from both tables, and I want columns from tableA to get NULL
> > values for tableB and vice versa.
> >
> > Example:
> >
> > tableA contents (comma-separated, with header):
> > columnA, common
> > 'From A', 123
> >
> > tableB contents (comma-separated, with header):
> > columnB, common
> > 'From B', 234
> >
> > I want a query which returns the following result set (ordering of the
> > columns is unimportant):
> >
> > columnA, columnB, common
> > 'From A', NULL, 123
> > NULL, 'From B', 234
> >
> >
> > I know I can accomplish this by using the following query:
> >
> > SELECT columnA, NULL AS columnB, common FROM tableA
> > UNION ALL
> > SELECT NULL AS columnA, columnB, common FROM tableB
> >
> >
> > But in my real-world scenario, there are lots of columns and three
tables
> > involved in the union. Therefore, I'm curious as to whether or not there
> are
> > any other methods of accomplish the same thing, one which doesn't
involve
> > specifying all columns for each table?
> >
> > Thanks!
> >
> >
> > Regards,
> > Nils Magnus ENglund
> >
> >
>
>


 
 
REMOVE_BEFORE_REPLYING_dportas





PostPosted: Mon Nov 29 08:33:07 CST 2004 Top

SQL Server Developer >> Unions You could use a FULL JOIN, like this:

SELECT A.col1, A.col2, A.col3, B.col1, B.col2, B.col3,
COALESCE(A.common, B.common) AS common
FROM TableA AS A
FULL JOIN TableB AS B
ON 0=1

In a FULL JOIN you wouldn't have to list all the columns individually, you
could use SELECT *, however it is good practice never to use SELECT * in
production code. In Query Analyzer you can drag the list of column names from
the Object Browser into the editing window. So you shouldn't have to do much
typing.

--
David Portas
SQL Server MVP
--
 
 
Toby





PostPosted: Mon Nov 29 08:37:15 CST 2004 Top

SQL Server Developer >> Unions Based on your description, I don't think you want a union. You want a full
outer join:

SELECT
TA.columnA,
TB.columnB,
ISNULL(TA.common, TB.common) AS common
FROM
tableA TA
FULL OUTER JOIN tableB TB
ON TB.common = TA.common
ORDER BY
ISNULL(TA.common, TB.common)

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e




> Hi!
>
> I have two tables I want to union, tableA and tableB. Apart from a single
> column 'common', the tables have different column names. I want to select
> all columns from both tables, and I want columns from tableA to get NULL
> values for tableB and vice versa.
>
> Example:
>
> tableA contents (comma-separated, with header):
> columnA, common
> 'From A', 123
>
> tableB contents (comma-separated, with header):
> columnB, common
> 'From B', 234
>
> I want a query which returns the following result set (ordering of the
> columns is unimportant):
>
> columnA, columnB, common
> 'From A', NULL, 123
> NULL, 'From B', 234
>
>
> I know I can accomplish this by using the following query:
>
> SELECT columnA, NULL AS columnB, common FROM tableA
> UNION ALL
> SELECT NULL AS columnA, columnB, common FROM tableB
>
>
> But in my real-world scenario, there are lots of columns and three tables
> involved in the union. Therefore, I'm curious as to whether or not there
> are any other methods of accomplish the same thing, one which doesn't
> involve specifying all columns for each table?