How to use MySQL LEFT JOIN to simulate MySQL MINUS query?-Mysql Tutorial-php.cn

How to use MySQL LEFT JOIN to simulate MySQL MINUS query?

王林

Release： 2023-09-16 11:09:15

forward

768 people have browsed it

如何使用MySQL LEFT JOIN 来模拟MySQL MINUS 查询？

Since we cannot use MINUS queries in MySQL, we will use LEFT JOIN to simulate MINUS queries. It can be understood with the help of the following example:

Example

In this example, we have two tables i.e. Student_detail and Student_info with the following data-

mysql> Select * from Student_detail;
+-----------+---------+------------+------------+
| studentid | Name    | Address    | Subject    |
+-----------+---------+------------+------------+
| 101       | YashPal | Amritsar   | History    |
| 105       | Gaurav  | Chandigarh | Literature |
| 130       | Ram     | Jhansi     | Computers  |
| 132       | Shyam   | Chandigarh | Economics  |
| 133       | Mohan   | Delhi      | Computers  |
| 150       | Rajesh  | Jaipur     | Yoga       |
| 160       | Pradeep | Kochi      | Hindi      |
+-----------+---------+------------+------------+
7 rows in set (0.00 sec)

mysql> Select * from Student_info;
+-----------+-----------+------------+-------------+
| studentid | Name      | Address    | Subject     |
+-----------+-----------+------------+-------------+
| 101       | YashPal   | Amritsar   | History     |
| 105       | Gaurav    | Chandigarh | Literature  |
| 130       | Ram       | Jhansi     | Computers   |
| 132       | Shyam     | Chandigarh | Economics   |
| 133       | Mohan     | Delhi      | Computers   |
| 165       | Abhimanyu | Calcutta   | Electronics |
+-----------+-----------+------------+-------------+
6 rows in set (0.00 sec)

Copy after login

Now, The following query using LEFT JOIN will simulate MINUS to return the "studentid" value in Student_info, but not the value in the Student_detail table.

mysql> SELECT studentid from student_info LEFT JOIN Student_detail USING(studentid) WHERE student_detail.studentid IS NULL;
+-----------+
| studentid |
+-----------+
|       165 |
+-----------+
1 row in set (0.07 sec)

Copy after login

Now, the following query will give us the opposite result of the above query i.e. it will return the "studentid" value in Student_detail but will not return the value in Student_info table.

mysql> SELECT studentid from student_detail LEFT JOIN Student_info USING(studentid) WHERE student_info.studentid IS NULL;
+-----------+
| studentid |
+-----------+
|       150 |
|      160  |
+-----------+
2 rows in set (0.00 sec)

Copy after login

The above is the detailed content of How to use MySQL LEFT JOIN to simulate MySQL MINUS query?. For more information, please follow other related articles on the PHP Chinese website!