MySQL-এ LEFT JOIN ব্যবহার করে MINUS কোয়েরি সিমুলেট করা

MySQL-এ MINUS (বা EXCEPT) সেট অপারেশন নেই। এর বিকল্প হিসেবে LEFT JOIN + WHERE ... IS NULL প্যাটার্ন ব্যবহার করে "টেবিল A-তে আছে কিন্তু টেবিল B-তে নেই" এমন রো বের করা যায়।

টেবিল: Student_detail এবং Student_info

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      |
+-----------+---------+------------+------------+

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 |
+-----------+-----------+------------+-------------+

Student_info-তে আছে কিন্তু Student_detail-এ নেই (MINUS সিমুলেশন)

mysql> SELECT studentid from student_info
    -> LEFT JOIN Student_detail USING(studentid)
    -> WHERE student_detail.studentid IS NULL;
+-----------+
| studentid |
+-----------+
| 165       |
+-----------+

Student_detail-এ আছে কিন্তু Student_info-তে নেই (উল্টো MINUS)

mysql> SELECT studentid from student_detail
    -> LEFT JOIN Student_info USING(studentid)
    -> WHERE student_info.studentid IS NULL;
+-----------+
| studentid |
+-----------+
| 150       |
| 160       |
+-----------+

প্যাটার্ন: SELECT a.col FROM a LEFT JOIN b ON a.key = b.key WHERE b.key IS NULL; — এটি a - b (MINUS) দেয়।