Display records from two columns based on comparison in MySQL?

Sharon Christine

Answered 4h 13m ago

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> Num1 int,
   -> Num2 int
   -> );
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(100,200);
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable values(200,100);
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable values(300,400);
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable values(400,300);
Query OK, 1 row affected (0.20 sec)

mysql> insert into DemoTable values(500,600);
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable values(600,500);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------+------+
| Num1 | Num2 |
+------+------+
|  100 |  200 |
|  200 |  100 |
|  300 |  400 |
|  400 |  300 |
|  500 |  600 |
|  600 |  500 |
+------+------+
6 rows in set (0.00 sec)

Following is the query that compares values from both the columns −

mysql> select *from DemoTable where Num1 < Num2;

This will produce the following output −

+------+------+
| Num1 | Num2 |
+------+------+
|  100 | 200  |
|  300 | 400  |
|  500 | 600  | 
+------+------+
3 rows in set (0.00 sec)