Compare 2 tables in sql

问题:

I have two tables, A1 and A2. I want to compare these two tables. I tried inner join but it doesn't give the required result.

These are the data in these tables,

Table A1

No. Address 
1  abc
1  abc
1  def
1  def

Table A2

No. Address
1    def
1    abc
1    abc
1    def

These two tables can only be joined by using No. column. So if I use INNER JOIN it gives 16 rows. I don't want that, I want only 4 rows to be displayed. This should be the output:

No.   Address   eq
1     abc      #
1     abc      *
1     abc      #
1     abc      #

Last column is displayed if address in A1 is equal to A2

回答1:

Search for records that exist in A1 table but not in A2 table:

SELECT * FROM A1 WHERE NOT EXISTS (SELECT * FROM A2 WHERE A2.Id = A1.Id)

回答2:

try to use case

select case when tablea1.address=tablea2.address then '*' else '#' end as eq from tablea1 inner join tablea2 on tablea1.=tablea2.

hope it helps you.