查询不同结构两表不同记录。该怎么处理

查询不同结构两表不同记录。
我有这样的两个表：
Table A:
name Dept age
----------------
mary IT 23
henry fs 25
joyce pr 28

Table B:
name Dept Sex
----------------
mary HR F
henry fs m
joyce ps F
我现在想要把这两个表比较，
得出不同记录，并放在一起比较，
并能多出一个字段Remark做判断是否相同。
得出结果如下：

name Dept_A Dept_B age sex Remark
-------------------------------------
mary IT HR 23 f N
henry fs fs 25 m Y
joyce pr ps 28 f N

Remark就表示比较是否Dept一样，如果一样就标记Y,如果不一样就N.
可以么？请教各位了。

[解决办法]

SQL code

select name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,       case when a.dept=b.dept then Y' else 'N' end as Remark from a join b on a.name=b.name
[解决办法]
SQL code
select A.name,A.Dept as Dept_A,B.Dept as Dept_B,A.age,B.Sex,(case when A.Dept=B.Dept then 'Y' else 'N' end) as Remark from A,B where A.name=B.name
[解决办法]
SQL code
select a.name,a.dept as dept_A,b.dept as dept_B,a.age,b.sex,(case when a.dept=b.dept then 'Y' else 'N' end) as Remarkfrom tableA a inner join tableB b on a.name=b.name
[解决办法]
SQL code
select    name,    Dept_A=a.dept,    Dept_B=b.dept,    age,    sex,    Remark =case when a.dept=b.dept then 'Y' else 'N' endfrom a     join b       on a.name=b.name
[解决办法]
SQL code
declare @A table(name varchar(10),Dept varchar(10),age int)insert into @A select 'mary ','IT',23 insert into @A select 'henry','fs',25 insert into @A select 'joyce','pr',28 declare @B table(name varchar(10),Dept varchar(10),sex char(1))insert into @B select 'mary ','HR','F' insert into @B select 'henry','fs','m' insert into @B select 'joyce','ps','F' select     A.name,A.Dept as Dept_A,B.Dept as Dept_B,A.age,B.Sex,    (case when A.Dept=B.Dept then 'Y' else 'N' end) as Remark from     @A A,@B B where     A.name=B.name/*name       Dept_A     Dept_B     age         Sex  Remark ---------- ---------- ---------- ----------- ---- ------ mary       IT         HR         23          F    Nhenry      fs         fs         25          m    Yjoyce      pr         ps         28          F    N*/
[解决办法]
SQL code
select name, =a.dept   , Dept_B=b.dept , age ,sex ,       case when a.dept=b.dept then Y' else 'N' end as Remark from tb1 a join tb2 b on a.name=b.name
[解决办法]
SQL code
if object_id('a') is not null  drop table agocreate table a(name varchar(10), Dept varchar(10), age int)---------------- insert a select 'mary','IT',23insert a select 'henry','fs',25insert a select 'joyce','pr',28goif object_id('b') is not null  drop table bgocreate table b(name varchar(10), Dept varchar(10), Sex varchar(10))insert b select 'mary','HR','F'insert b select 'henry','fs','m'insert b select 'joyce','ps','F'goselect    a.name,    Dept_A=a.dept,    Dept_B=b.dept,    age,    sex,    Remark =case when a.dept=b.dept then 'Y' else 'N' endfrom a     join b       on a.name=b.name/*name       Dept_A     Dept_B     age         sex        Remark---------- ---------- ---------- ----------- ---------- ------mary       IT         HR         23          F          Nhenry      fs         fs         25          m          Yjoyce      pr         ps         28          F          N(3 行受影响)*/