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: 
Question from the application:
- 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?
select 'aaa' as FIRSTNAME, 'bbb' as LASTNAME ... UNION select ...- no table used but results are the same. For that you might need at least some simple parser. (Even using EXPLAIN on the query is not 100% imho, because EXPLAIN shows table aliases, but it would catch this counterexample at least). And comparing the data itself would be quite a challenge in sql - you might use some natural joins and similar stuff, but handling it in app code seems much simpler. – jkavalik Aug 25 '15 at 19:42