Oracle中INTERSECT用法疑惑：同表查询为何仅返回去重数据？

Got it, let's unpack what's going on here. When you run:

select id,name,age from ot.managers_temp intersect select id,name,age from ot.managers_temp

you're essentially asking SQL to find the overlap between the table and itself. But here's the critical thing about standard SQL's INTERSECT operator: it automatically strips out duplicate rows from the final result, just like adding DISTINCT to a regular SELECT.

Why Your Expected Result Isn't Showing Up #

If your original ot.managers_temp table has duplicate entries (like multiple rows with 1 ashwin 21 or 4 saman 21), INTERSECT will collapse those duplicates into one row each. That's because INTERSECT is built to return only unique rows that exist in both result sets—even when both sets are coming from the exact same table.

How to Get Duplicates in Your Intersection Result #

If you want to keep duplicate rows that appear in both queries (which, in this case, means all rows from the table including duplicates), you need to use INTERSECT ALL instead. This version preserves duplicate rows based on how many times they show up in both of the input result sets.

Update your query to this:

select id,name,age from ot.managers_temp intersect all select id,name,age from ot.managers_temp

This will return every row from your table, including duplicates, which should align with the output you expected (1 ashwin 21 and 4 saman 21, including any repeats you were hoping to see).

Quick Cheat Sheet #

• INTERSECT: Returns unique shared rows (removes duplicates)
• INTERSECT ALL: Returns all shared rows, duplicates included (as long as the duplicate count matches across both queries)

内容的提问来源于stack exchange，提问作者Random guy