Sometimes you may need to compare two columns from same table or different table, in MySQL. MySQL allows you to easily compare two columns using operators as well as nested queries. In this article, we will look at how to compare two columns in MySQL.
How to Compare Two Columns in MySQL
We will look at different use cases to compare two columns in MySQL
Compare two columns from same table
Let’s say you have the following table sales(id, cost_price, selling_price) and you want to compare cost_price and selling_price columns.
mysql> create table sales(id int, cost_price int, selling_price int);
mysql> insert into sales(id, cost_price, selling_price)
values(1, 135, 215),
(2,215, 145),
(3,310,100);
mysql> select * from sales;
+------+------------+---------------+
| id | cost_price | selling_price |
+------+------------+---------------+
| 1 | 135 | 215 |
| 2 | 215 | 145 |
| 3 | 310 | 100 |
+------+------------+---------------+
Here’s the generic SQL query to two compare columns (column1, column2) in a table (table1).
mysql> select * from table1
where column1 not in
(select column2 from table1);
In the above query, update table1, column1 and column2 as per your requirement.
Bonus Read : How to Get Every Nth Row in MySQL
We will apply this query to compare two columns cost_price and selling_price, and display records where there is a mismatch between two columns.
mysql> select * from sales
where cost_price not in
(select selling_price from sales);
+------+------------+---------------+
| id | cost_price | selling_price |
+------+------------+---------------+
| 1 | 135 | 215 |
| 3 | 310 | 100 |
+------+------------+---------------+
You can also use mathematical operators (<, >, <>) in case you want to compare two numerical columns. Here’s an example to display rows where cost_price>selling_price.
mysql> select * from sales where cost_price>selling_price; +------+------------+---------------+ | id | cost_price | selling_price | +------+------------+---------------+ | 2 | 215 | 145 | | 3 | 310 | 100 | +------+------------+---------------+
Bonus Read : How to Add Auto Increment Column In MySQL
Compare two columns from different table
Let us say you also have another table orders(id, cost_price, selling_price)
mysql> create table orders(id int, cost_price int, selling_price int);
mysql> insert into orders(id, cost_price, selling_price)
values(1, 235, 215),
(2,205, 105),
(3,320,120);
mysql> select * from orders;
+------+------------+---------------+
| id | cost_price | selling_price |
+------+------------+---------------+
| 1 | 235 | 215 |
| 2 | 205 | 105 |
| 3 | 320 | 120 |
+------+------------+---------------+
Bonus Read : Top 5 Free Database Design Tools
Here’s the above SQL query modified to compare two columns from different tables table1 and table2
mysql> select * from table1
where column1 not in
(select column2 from table2);
We will apply the above query to compare cost_price column from sales table with selling_price with orders table.
mysql> select * from sales
where cost_price not in
(select selling_price from orders);
+------+------------+---------------+
| id | cost_price | selling_price |
+------+------------+---------------+
| 1 | 135 | 215 |
| 3 | 310 | 100 |
+------+------------+---------------+
Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
