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: 
I would do it like here:

```
select coalesce(t1.id, t2.id) id, t1.name new_name, t2.name old_name,
    case when t1.id is null then 'row deleted'
         when t2.id is null then 'row inserted'
         when t1.name <> t2.name  
           or t1.name is null and t2.name is not null
           or t1.name is not null and t2.name is null
         then 'data changed'
    end change
  from t1 full join t2 on t1.id = t2.id order by id

```

[SQLFiddle](http://sqlfiddle.com/#!4/baa614/1)

And by the way- what's so hard in formatting data in your questions? Just add 4 spaces before something and this will be indicated as code. Or select some part and press [Ctrl-K]. Everything is rendered as you write it.
```
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;

```