Outer Join woes

/*
Hi -
Is there a better way to get the desired results?
Thanks,
John
-- contents T1
C1 C2 A
-- -- --
1 1 100
1 2 100
-- contents T2
C1 C2 B
-- -- --
1 1 500
1 3 500
-- desired results
C1 C2 A B
-- -- -- --
1 1 100 500
1 2 100 NULL
1 3 NULL 500
*/
SET NOCOUNT ON
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T1 (C1 int, C2 int, A int)
CREATE TABLE T2 (C1 int, C2 int, B int)
INSERT T1 VALUES (1,1, 100)
INSERT T1 VALUES (1, 2, 100)
INSERT T2 VALUES (1,1, 500)
INSERT T2 VALUES (1, 3, 500)
SELECT * FROM T1
SELECT * FROM T2
-- there's gotta be a better way!
SELECT CASE WHEN T1.C1 IS NULL THEN T2.C1 ELSE T1.C1 END 'C1',
CASE WHEN T1.C2 IS NULL THEN T2.C2 ELSE T1.C2 END 'C2',
T1.A,
T2.B
FROM T1
FULL OUTER JOIN t2
ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
ORDER BY 1,2
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
SET NOCOUNT OFFYou can use function COALESCE to make it more readable.
SELECT
coalesce(T1.C1, T2.C1) as 'C1',
coalesce(T1.C2, T2.C2) as 'C2',
T1.A,
T2.B
FROM
T1
FULL OUTER JOIN t2
ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
ORDER BY
C1,
C2
AMB
"John Beatty" wrote:

> /*
> Hi -
> Is there a better way to get the desired results?
> Thanks,
> John
> -- contents T1
> C1 C2 A
> -- -- --
> 1 1 100
> 1 2 100
> -- contents T2
> C1 C2 B
> -- -- --
> 1 1 500
> 1 3 500
> -- desired results
> C1 C2 A B
> -- -- -- --
> 1 1 100 500
> 1 2 100 NULL
> 1 3 NULL 500
> */
> SET NOCOUNT ON
> IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
> IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
> CREATE TABLE T1 (C1 int, C2 int, A int)
> CREATE TABLE T2 (C1 int, C2 int, B int)
> INSERT T1 VALUES (1,1, 100)
> INSERT T1 VALUES (1, 2, 100)
> INSERT T2 VALUES (1,1, 500)
> INSERT T2 VALUES (1, 3, 500)
> SELECT * FROM T1
> SELECT * FROM T2
> -- there's gotta be a better way!
> SELECT CASE WHEN T1.C1 IS NULL THEN T2.C1 ELSE T1.C1 END 'C1',
> CASE WHEN T1.C2 IS NULL THEN T2.C2 ELSE T1.C2 END 'C2',
> T1.A,
> T2.B
> FROM T1
> FULL OUTER JOIN t2
> ON T1.C1 = T2.C1 AND T1.C2 = T2.C2
> ORDER BY 1,2
> IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
> IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
> SET NOCOUNT OFF
>
>

Other than UNION

Hello,

Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)
solution than UNION operator in this case. TIA.

-- DDL
CREATE TABLE #TMP (col varchar(10));

-- DML
insert into #TMP
values('A124');

insert into #TMP
values('A127');

insert into #TMP
values('A12728');

insert into #TMP
values('A17282');

insert into #TMP
values('BCD');

insert into #TMP
values('BCD');

insert into #TMP
values('CDSS');

insert into #TMP
values('DS');

insert into #TMP
values('YUUEI');

-- goal: get one row with col data starting with 'A' and distict rows
for the rest

select top 1 col
from #TMP
where col LIKE 'A%'

UNION

select distinct col
from #TMP
where col NOT LIKE 'A%'On 16 Mar 2005 11:19:33 -0800, NickName wrote:

>Bear with me (not had much sleep last night), pls see following ddl,
>dml and comments for what is desired, I don't have a problem getting
>desired result(s), however, I'm wondering if there's another (better)
>solution than UNION operator in this case. TIA.

Hi Nick,

Well, using UNION ALL instead of UNION would be the obvious improvement.
Or you could use the following (completely different) approach. Test it
in your environment to see which version performs best.

select distinct col
from #TMP AS t1
where col not like 'A%'
or not exists (select *
from #TMP AS t2
where col like 'A%'
and t2.col < t1.col)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the response, Hugo. I was not paying enough attention. The
"DISTINCT" key word is not necessary in previous posting because UNION
would return unique records anyway, as for UNION ALL, it does not seem
applicable here because it would return duplicate rows as well, which
was/is not desired.

Don

Hugo Kornelis wrote:
> On 16 Mar 2005 11:19:33 -0800, NickName wrote:
> >Bear with me (not had much sleep last night), pls see following ddl,
> >dml and comments for what is desired, I don't have a problem getting
> >desired result(s), however, I'm wondering if there's another
(better)
> >solution than UNION operator in this case. TIA.
> Hi Nick,
> Well, using UNION ALL instead of UNION would be the obvious
improvement.
> Or you could use the following (completely different) approach. Test
it
> in your environment to see which version performs best.
> select distinct col
> from #TMP AS t1
> where col not like 'A%'
> or not exists (select *
> from #TMP AS t2
> where col like 'A%'
> and t2.col < t1.col)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 16 Mar 2005 18:16:12 -0800, NickName wrote:

>Thanks for the response, Hugo. I was not paying enough attention. The
>"DISTINCT" key word is not necessary in previous posting because UNION
>would return unique records anyway, as for UNION ALL, it does not seem
>applicable here because it would return duplicate rows as well, which
>was/is not desired.

Hi Don,

In your original code, your first query will always return one row, so
it can't have duplicates; the second uses DISTINCT to eliminate
duplicates. But since you don't use UNION ALL, the server still has to
check if the row from the first result set is also in the second result
set, and eliminate it if it is. You and I know that this can never
happen (as a result of mutual exclusive WHERRE clauses), so we can save
the server some work by adding ALL to the UNION keyword.

If you remove the DISTINCT from the second query, then you must indeed
use UNION, not UNION ALL. You still might to check if there's a big
difference in execution times (probably not, but you never know). Oh,
and do include my UNION-less version in your performance tests as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo, I appreciate your opinion. As my original posting indicated
"I was not in thinking mode when I composed the msg", for just now when
I took a closer look, I notice that the data set is small, hence,
almost "any" query would do the job well, not really necessarily for
optimization, Query Analyzer wasn't even able to calculate execution
time, however, for large data set, your other approach would be
meaningful to try. Again thanks.

Don