Question: I want to write a select query that will compare the two table. By comparing it should tell that what record are need to be updated, inserted or deleted from table 2. 

Contents of the tables:

```
Table 1
----- ---------
 id    name
----- ---------
 1     shubham
 2     ravi
 3     aman
 4     vijay

Table 2
----- ---------
 id    name
----- ---------
 1     shubham
 2     ravi
 3     aman
 4     vijay

```

now both the table has same data now when data is changed in `table1` like

```
Table 1
----- ---------
 id    name
----- ---------
 1     shubham
 2     harish
 3     aman                        
 5     saurabh

```

Now here in table1 name of id 2 is updated and id 4 is deleted also id is inserted. I want my query to select all these records from table 1 also tell whether which operation is to be done. I dont want to use set operator also. please help

Answer: 
No set operators involved in this solution, but they are probably easier to use when there are more than just a couple of columns to compare:

```
select coalesce(s.id, d.id) id
     , coalesce(s.name, d.name) name
     , case when s.id is null then 'D'
            when d.id is null then 'C'
            when s.name != d.name then 'U'
       end CUD
  from table1 s
  full join table2 d
    on s.id = d.id
 where s.id is null
    or d.id is null
    or s.name != d.name

```

The `CUD` column just indicates the operation to carry out `C`reate, `U`pdate, or `D`elete. The more flexible set based solution would be something like this:

```
select 'CU' op, s.* from table1 s
minus
select 'CU' op, d.* from table2 d
union
select 'D' op, d.* from table2 d where d.id not in (select s.id from table1 s)

```

In this case you don't know if the table1 records left after the minus operation are new or changed so the op is either `C`reate or `U`pdate, but you still definitively know the `D`elete operations.

Either one of these queries could be used in the `USING` clause of a [`MERGE`](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606) statement to update `table2` to match `table1`
```
SELECT 
TEST1.NAME,TEST1.CLASS,test2.name,test2.class,
case  when test1.name 
is null then 'deleted'  
when UPPER(test1.name)!=UPPER(test2.name) then 'insert'
when test2.name is null then 'updated' ELSE 'no change' end as flag
FROM    TEST1
full outer join TEST2
  ON  
 test2.class=test1.class;

```