how to use the minus operator

"sql_learner" <mailfrd@.gmail.com> wrote in message
news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
> hi...i can up into a situation where i have to use the MINUS operator
> but i don't find that in sql...is there any alternatives,,,i tried NOT
> IN but it just returned me one column and i have multiple changes in
> my two tables like
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> a 02
> b 01
> c 04
> CREATE TABLE T1 (EMP(VARCHAR (20) NOT NULL, DEPT(VARCHAR(2) NOT NULL)
> emp dept
> a 01
> b 04
> d 05
> output table:
> emp dept
> a 02
> b 04
> d 05
> emp 'a' with dept '02' is removed, dept of emp 'b' is changed and new
> emp 'd' is added. I need all the changed in the table. thanks for
> sharing...
SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
DIFFERENCE - as in Oracle for example).
I can't work out what that has to do with the result you asked for. If
EXCEPT doesn't answer your question then maybe you could explain the logic
you are tring to apply.
David Portas
i am just looking for all the changes that exists in both the
tables...i tried
select * from t1 a
left outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
but this gives me only the changes that is in the second table t2 and
not the other way round. i can again use right join and finally join
them but i was hoping there might be a better way to find all the
changes there are. and also comparing all the columns is little
clumsy..what if there is a lot of tables...so if u know something
that could do this then please help me...thanks
|||On Dec 19, 5:20 pm, babu.dah...@.gmail.com wrote:
> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
my mistake...i meant "what if there are a lot of COLUMNS not tables"
in my previous post
|||On Dec 19, 5:05 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "sql_learner" <mail...@.gmail.com> wrote in message
> news:bb6b2f30-aebc-4361-9dd7-6dec8d9ddfbc@.s19g2000prg.googlegroups.com...
>
>
>
>
>
> SQL Server's EXCEPT operator is the equivalent of relational MINUS (aka
> DIFFERENCE - as in Oracle for example).
> I can't work out what that has to do with the result you asked for. If
> EXCEPT doesn't answer your question then maybe you could explain the logic
> you are tring to apply.
> --
> David Portas
like mr. babu.dah...@.gmail.com said..i used join, not exists but both
gives changes in one table and not both...i wanted to try EXCEPT but
it is not taking it...i don't know what is wrong...i tried
select * from t1
except (select * from t2)
and also
select emp from t1
except (select emp from t2)
please correct me with my syntax...thanks
|||This should work for you
SELECT emp,dept
FROM emp1
WHERE NOT EXISTS
(SELECT 1 FROM emp2 WHERE emp1.empname = emp2.empname and emp1.dept =
emp2.dept)
- Sha Anand
"sql_learner" wrote:

> On Dec 19, 5:05 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> like mr. babu.dah...@.gmail.com said..i used join, not exists but both
> gives changes in one table and not both...i wanted to try EXCEPT but
> it is not taking it...i don't know what is wrong...i tried
> select * from t1
> except (select * from t2)
> and also
> select emp from t1
> except (select emp from t2)
> please correct me with my syntax...thanks
>
|||select *
from t1 a
full outer join
t2 b on (a.emp=b.emp and a.dept=b.dept)
where b.emp is null
should give you both sides
"babu.dahal1@.gmail.com" wrote:

> i am just looking for all the changes that exists in both the
> tables...i tried
> select * from t1 a
> left outer join
> t2 b on (a.emp=b.emp and a.dept=b.dept)
> where b.emp is null
> but this gives me only the changes that is in the second table t2 and
> not the other way round. i can again use right join and finally join
> them but i was hoping there might be a better way to find all the
> changes there are. and also comparing all the columns is little
> clumsy..what if there is a lot of tables...so if u know something
> that could do this then please help me...thanks
>