Bagaimanakah kita boleh mensimulasikan pertanyaan MySQL INTERSECT yang mengembalikan berbilang ungkapan?

Oleh kerana kami tidak boleh menggunakan pertanyaan INTERSECT dalam MySQL, kami akan menggunakan operator EXIST untuk mensimulasikan pertanyaan INTERSECT. Ia boleh difahami melalui contoh berikut -

Contoh

Dalam contoh ini kita mempunyai dua jadual iaitu Student_detail dan Student_info dengan data berikut-

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)

Sekarang, gunakan operator EXIST dengan klausa WHERE Pertanyaan berikut akan mensimulasikan INTERSECT kembalikan "studentid", nama, alamat (di mana nama bukan "Yashpal") yang wujud dalam kedua-dua jadual -

mysql>Select Student_detail.studentid,Student_detail.name, student_detail.address FROM student_detail WHERE Student_detail.studentid >100 AND EXISTS (SELECT * FROM Student_info WHERE Student_info.Name <> &#39;Yashpal&#39; AND Student_info.studentid = Student_detail.studentid AND Student_info.name = Student_detail.name);
+-----------+--------+------------+
| studentid | name   | address    |
+-----------+--------+------------+
|       105 | Gaurav | Chandigarh |
|       130 | Ram    | Jhansi     |
|       132 | Shyam  | Chandigarh |
|       133 | Mohan  | Delhi      |
+-----------+--------+------------+
4 rows in set (0.00 sec)

Atas ialah kandungan terperinci Bagaimanakah kita boleh mensimulasikan pertanyaan MySQL INTERSECT yang mengembalikan berbilang ungkapan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!