Re: Hi! All

DA Morgan wrote:

> Abha wrote:
> > Can anyone send me SQL Question papers as I am preparing for OCP
> > Examination.
>
> Sure ... here's my favorite.
>
> You have two tables that are absolutely identical in structure. Each
> contains rows of data ... some of which are identical between the
> tables and some of which are not. Find the rows that are distinct and
> in one query return the distinct rows from both tables and the name
> of the table that contained them.
>
> CREATE TABLE t1 (
> col1 VARCHAR2(5));
>
> CREATE TABLE t2 (
> col1 VARCHAR2(5));
>
> INSERT INTO t1 VALUES ('A');
> INSERT INTO t1 VALUES ('B');
> INSERT INTO t1 VALUES ('C');
> INSERT INTO t2 VALUES ('A');
> INSERT INTO t2 VALUES ('C');
> INSERT INTO t2 VALUES ('D');
> COMMIT;
>
> The result from the single query should be:
>
> A T1
> D T2
>
> Enjoy!
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

how about the full outer join approach?

SELECT CASE WHEN T1.col1 IS NULL THEN T2.col1 ELSE T1.col1 END col1,

       CASE WHEN T1.col1 IS NULL THEN 'T2' ELSE 'T1' END table_nm   FROM T1
  FULL OUTER JOIN T2
    ON T2.col1 = T1.col1
 WHERE T1.col1 IS NULL OR T2.col1 IS NULL

The first CASE expression can be replaced with a call to the COALESCE function:

COALESCE(T1.col1, T2.col1)

---
Andrey Odegov
avodeGOV_at_yandex.ru
(remove GOV to respond)