How to compare two columns in MySQL

In MySQL, we can compare two columns  to identify the records they have in common and also, the records one table has but the other does not.

In this article, we will learn how to compare two columns to find common records, and we will also find their differences.

To illustrate the examples in this article, we are going to create two tables (contacts and clients) with the following structure and data:

1. CREATE TABLE `clients` (
2. `id` bigint(20) UNSIGNED NOT NULL,
3. `name` varchar(255) DEFAULT NULL,
4. `address` text
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `clients` (`id`, `name`, `address`) VALUES
6. (1, 'Mark Johnson', '2113 Jackson Street,Apt. B,Erie,CA,510'),
7. (2, 'Sasha Smith', '123 Main Street,,Erie,CA,512'),
8. (3, 'Charles Walls', '979 Drummond Street, Newark'),
9. (4, 'Susan Lee', '1572 Hanover Street'),
10. (5, 'Carol Parker', '4834 Blackwell Street');
11. CREATE TABLE `contacts` (
12. `id` int(11) NOT NULL,
13. `name` varchar(20) NOT NULL,
14. `phone` varchar(20) NOT NULL,
15. `email` varchar(20) NOT NULL
16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18. INSERT INTO `contacts` (`id`, `name`, `phone`, `email`) VALUES
19. (1, 'John Grey', '19005264555', 'john@gmail.com'),
20. (2, 'Mary Rose', '19008504587', 'mary@gmail.com'),
21. (3, 'Matt Crow', '19006238747', 'matt@gmail.com'),
22. (4, 'Susan Lee', '19006781563', 'susan@gmail.com'),
23. (5, 'Carol Parker', '19007426336', 'carol@gmail.com'),
24. (6, 'Mark Johnson', '19008881234', 'mark@gmail.com'),
25. (7, 'Sasha Smith', '19001527788', 'sasha@gmail.com'),
26. (8, 'Isaac Peterson', '19008795678', 'isaac@gmail.com'),
27. (9, 'Ana Brown', '19006961425', 'ana@gmail.com'),
28. (10, 'Alex Robertson', '19003693791', 'alex@gmail.com');

We can execute the following queries to verify the content of each table:

1. SELECT * FROM `clients`

Result:

Clients table content

1. SELECT * FROM `contacts`

Result:

Contacts table content

Compare two columns in sql

Let’s compare the “NAME” column of both tables to find the names they have in common, and the names that one table has and the other does not.

To find the names that both tables have in common, we will execute the following query:

1. SELECT a.name
2. FROM (SELECT distinct b.name FROM `contacts` b UNION ALL
3. SELECT distinct c.name FROM `clients` c) a
4. GROUP BY a.name
5. HAVING count(*) = 2
6. ORDER BY a.name

Result:

Common names between both tables

As we can see, there are four names in common between both tables. The logic of the query we use to find them is very simple.

First, we request the names in the contacts table, to avoid repeated names we included the DISTINCT clause. Then we made a union with the names in the clients table. Finally, we filter the names that have two occurrences, that is, the names that are in the two tables.

Now, to calculate the differences between the two name columns, we can execute a very similar query, but we will filter the names that have a single occurrence, that is, the names that are in only one table:

1. SELECT a.name
2. FROM (SELECT distinct b.name FROM `contacts` b UNION ALL
3. SELECT distinct c.name FROM `clients` c) a
4. GROUP BY a.name
5. HAVING count(*) = 1
6. ORDER BY a.name

Result:

Name differences

As we can see, there are seven names that are not common between the two tables.