Search code examples
mysqlsqlphpmyadminsubquerysqlexception

phpmyadmin EXCEPT SELECT is not usable

I am trying to use EXCEPT when getting data from 2 tables, but I can not use EXCEPT SELECT.

Phpmyadmin does not recognize EXCEPT and does not allow me to use the 2nd SELECT.

Is there other way i can check for same entries on the tables?

EDIT: if t1 has rows: 

id1-name1, id3-name3

and t2 has: 

id1-name1, id3-name3, id4-name4

then i would get a table with id4-name4 only (talking about rows)

Solution

  • MySQL does not support the EXCEPT syntax.

    A typical solution is to use NOT EXISTS instead. Assuming that both tables have a structure like (col1, col2, col3), you would go:

    select t2.*
from t2
where not exists (
    select 1 
    from t1
    where t2.col1 = t1.col1 and t2.col2 = t1.col2 and t2.col3 = t1.col3
)

    This returns rows from t2 where no row exists in t1 with the same tuple of values in the three columns.