How to get the common values from columns of two different tables?-CodePudding

I have two tables customers table and suppliers table. In both the tables, I have City column with different city names but there may be some cities which are present in both the tables. I want to retrieve only those values (city names) which are present in both. How can I get those values?

CodePudding user response：

You need to use the inner join, something like this

select customers.name, customers.city, suppliers.address
from customers
inner join suppliers on customers.city = suppliers.city

MySQL INNER JOIN Keyword The INNER JOIN keyword selects records that have matching values in both tables.

MySQL INNER JOIN Keyword

CodePudding user response：

I suggest to INNER JOIN two SELECT statements. Attention on the alias!

select distinct city from 
(select city from customers) c
inner join
(select city from suppliers) s
on c.city = s.city;