如何从三张含location字段的表中获取唯一的地点值？

Got it, let's break down how to get those location values that exist in all three of your tables (table1, table2, table3) — since your example shows Delhi present in every table and that's exactly the result you want. Here are a few reliable approaches depending on your database system:

Method 1: Use INNER JOINs #

This is a straightforward approach that works across most SQL databases:

SELECT DISTINCT t1.location
FROM table1 t1
INNER JOIN table2 t2 ON t1.location = t2.location
INNER JOIN table3 t3 ON t1.location = t3.location;

• The INNER JOIN ensures we only keep locations that have matches in all three tables.
• DISTINCT guarantees we get unique location values, even if a table has duplicate entries for the same location.

Method 2: Use INTERSECT (For Databases That Support It) #

If your database supports set operations like INTERSECT (e.g., PostgreSQL, SQL Server, Oracle), this is a clean and concise option:

SELECT location FROM table1
INTERSECT
SELECT location FROM table2
INTERSECT
SELECT location FROM table3;

• INTERSECT automatically returns only values that exist in all the query results, and it handles deduplication by default — no need for DISTINCT here.

Method 3: Use EXISTS Subqueries #

This is another widely compatible approach, great for databases that don't support INTERSECT (like older MySQL versions):

SELECT DISTINCT location
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.location = t1.location)
AND EXISTS (SELECT 1 FROM table3 t3 WHERE t3.location = t1.location);

• The EXISTS clauses check if the current location from table1 exists in table2 and table3 respectively.
• Again, DISTINCT ensures we only get each matching location once.

Note: #

If any of your tables have duplicate entries for the same location (e.g., multiple rows with "delhi" in table1), all the above methods will still return "delhi" only once, which aligns with your requirement for a unique result.

内容的提问来源于stack exchange，提问作者Bhagwan Singh