Re: Hi! All
Date: Thu, 6 Jul 2006 10:27:21 +0200
Message-ID: <e8ihk2$gif$1_at_ss408.t-com.hr>
"ChrisF" <chris.freel_at_gmx.ch> wrote in message
news:1152115368.636954.123810_at_75g2000cwc.googlegroups.com
> Can I play too?
>
Why not? :-)
> SELECT T1.COL1, 'T1' FROM T1, T2
> WHERE T1.COL1 = T2.COL1 (+) AND T2.COL1 IS NULL
> UNION
> SELECT T2.COL1, 'T2' FROM T1, T2
> WHERE T2.COL1 = T1.COL1 (+) AND T1.COL1 IS NULL;
>
> "NOT IN" constructs can be inefficient.
> Doing left/right joins and excluding the NULL values is in my
> experience faster.
Yes it is, especially on indexed columns. As well as 'NOT EXISTS'. Just for information, you can also try this subquery syntax used by ad-hoc query generators, but it shouldn't be any faster.
SELECT col1, 'T1' tab
FROM t1
WHERE NOT col1 = ANY (
SELECT col1
FROM t2
)
UNION ALL
SELECT col1, 'T2'
FROM t2
WHERE NOT col1 = ANY (
SELECT col1
FROM t1
);
Received on Thu Jul 06 2006 - 10:27:21 CEST