Difference between two selects in MySQL?

You can use subqueries for difference between two selects in MySQL. The syntax is as follows:

SELECT *FROM yourTableName where yourColumnName NOT IN(SELECT yourColumnName FROM youTableName WHERE yourCondition;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table DifferenceSelectDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> UserId int,
   -> UserValue int,
  -> PRIMARY KEY(Id)
   ->  );
Query OK, 0 rows affected (0.87 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(10,10);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(10,20);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(20,30);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(20,20);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(30,40);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DifferenceSelectDemo(UserId,UserValue) values(30,20);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from DifferenceSelectDemo;

The following is the output:

+----+--------+-----------+
| Id | UserId | UserValue |
+----+--------+-----------+
|  1 |     10 |        10 |
|  2 |     10 |        20 |
|  3 |     20 |        30 |
|  4 |     20 |        20 |
|  5 |     30 |        40 |
|  6 |     30 |        20 |
+----+--------+-----------+
6 rows in set (0.02 sec)

Here is the query to get the difference between two selects:

mysql> select *from DifferenceSelectDemo
   -> WHERE UserValue NOT IN (select UserValue from DifferenceSelectDemo where Id=1);

The following is the output:

+----+--------+-----------+
| Id | UserId | UserValue |
+----+--------+-----------+
|  2 |     10 |        20 |
|  3 |     20 |        30 |
|  4 |     20 |        20 |
|  5 |     30 |        40 |
|  6 |     30 |        20 |
+----+--------+-----------+
5 rows in set (0.09 sec)