How to find the common values from two different tables having a common column

Question

I have two tables table1 and table2. Both tables have a common column named city. How do I find all values under city which are in both the tables ?

There are literally thousands of tutorials online that cover this... — Siyual, Jul 21 '17 at 14:33
Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Brian J, Jul 21 '17 at 15:08

score 0 · Answer 1 · answered Jul 21 '17 at 14:41

You can do an inner join on the city column, to find values that exist in both tables.

select
    -- Output the city from either table (since it will be the same)
    t1.city 
from
    -- Join table1 and table2 together, on a matching city column
    table1 t1 join table2 t2 on (t1.city=t2.city)
group by 
    -- Only return a single row per city
    t1.city

score 0 · Answer 2 · answered Jul 21 '17 at 15:08

SELECT tbone.desired_column1
       tbone.desired_column2,
       --other columns from table one
       tbtwo.desired_column1,
       tbtwo.desired_column2
       --other columns from table two
 -- Bellow we're stating what this table could be identified as (tbone and tbtwo), so that you don't have to keep typing table name above and bellow. Can be anything, such as A or B or HORSECORRECTINGBATTERY
 FROM table1 tbone,  
      table2 tbtwo
 WHERE tbone.city = tbtwo.city

If you don't want to specify which columns to take, just go with

SELECT * FROM ...

How to find the common values from two different tables having a common column

2 Answers2