Returning only duplicate rows from two tables

Question

Every thread I've seen so far has been to check for duplicate rows and avoiding them. I'm trying to get a query to only return the duplicate rows. I thought it would be as simple as a subquery, but I was wrong. Then I tried the following:

SELECT * FROM a
WHERE  EXISTS
(
     SELECT * FROM b
     WHERE b.id = a.id
)

Was a bust too. How do I return only the duplicate rows? I'm currently going through two tables, but I'm afraid there are a large amount of duplicates.

Answer 1

use this query, maybe is better if you check the relevant column.

SELECT * FROM a

INTERSECT 

SELECT * FROM b

Answer 2

I am sure your posted code would work too like

SELECT * FROM a
WHERE  EXISTS
(
     SELECT 1 FROM b WHERE id = a.id
)

You can as well do a INNER JOIN like

SELECT a.* FROM a
JOIN b on a.id = b.id;

You can as well use a IN operator saying

SELECT * FROM a where id in (select id from b);

If none of them, then you can use UNION if both table satisfies the union restriction along with ROW_NUMBER() function like

SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
FROM (
select * from a

union all 

select * from b) xx ) yy
WHERE rn = 1;