Mysql – compare results between two select queries MySQL

MySQLPHP

I'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query.

"Correct answer" query is in database, and the comparable select is inputed by user.

I read about EXCEPT and INTERSECT but they don't exist in MySQL.

For example I have this database structure: db pic

Question from the application:

  1. List the first name, last name and date of birth of all female students whose first name is Yana but their last name is not Taylor.

correct answer-

SELECT FIRSTNAME, LASTNAME, DATEOFBIRTH 
FROM PERSON 
JOIN STUDENTS ON PERSONID=STUDENTID
WHERE GENDER=’F’ AND FIRSTNAME=’YANA’ AND LASTNAME<>’TAYLOR’

I want to check that the returned result is identical. wrong answer will be MySQLquery for example with aliased columns:

SELECT FIRSTNAME as first_name, LASTNAME as last_name, DATEOFBIRTH as date_of_birth ....

or even like statement (if it returns more results):

WHERE GENDER=’F’ AND FIRSTNAME LIKE '%YANA%' AND LASTNAME<>'TAYLOR'

My question is how to compare results between two queries that:

  • Their returned row count is the same
  • Their column names are equal
  • It's from the same table
  • And maybe return some indication for true or false that they are identical?

Best Answer

pt-table-checksum and its friends are very good at validating that the data is (or is not) the same.

Related Question