Why is this MySQL INTERSECTION with wrong syntax?

Question

I have two tables, a and b:

+-------------+--------+-----------+
| ID_do_aluno | Nome   | Sobrenome |
+-------------+--------+-----------+
|   154676754 | John   | Brown     |
|   232565690 | George | Yellow    |
|   345896580 | Anne   | Green     |
|   459986789 | Ted    | Purple    |
+-------------+--------+-----------+

+-------------+--------+-----------+
| ID_do_aluno | Nome   | Sobrenome |
+-------------+--------+-----------+
|   154676754 | John   | Brown     |
|   232565690 | George | Yellow    |
|   342889999 | Rich   | White     |
+-------------+--------+-----------+

Im trying to get their intersection. I tried

(select * a)INTERSECT(select * from b);

and many other variations, but couldnt get it right. What I need is

+-------------+--------+-----------+
| ID_do_aluno | Nome   | Sobrenome |
+-------------+--------+-----------+
|   154676754 | John   | Brown     |
|   232565690 | George | Yellow    |
+-------------+--------+-----------+

Thanks in advance, sorry for the begginer's difficulty to locate this well-discussed subject in foruns & documentation!

Answer 1

There is no intersect in MySQL. Instead, use exists or in:

select ID_do_aluno, Nome, Sobrenome
from a
where (a.ID_do_aluno, a.Nome, a.Sobrenome) in (select b.ID_do_aluno, b.Nome, b.Sobrenome from b)

Answer 2

The correct way to simulate INTERSECT which is not supported by MySql, is by comparing each column of a to its respective column of b with the null safe equal operator <=>, so that null values will be considered equal.
Also, INTERSECT, in databases that support it, returns distinct rows.

SELECT DISTINCT a.ID_do_aluno, a.Nome, a.Sobrenome
FROM a
WHERE EXISTS (
  SELECT 1 FROM b
  WHERE b.ID_do_aluno <=> a.ID_do_aluno AND b.Nome <=> a.Nome AND b.Sobrenome <=> a.Sobrenome
)

Answer 3

MySQL doesn't do INTERSECT , sadly.