比较两个SQL查询结果集

Question

So i have an application that checks SQL SELECT queries against some defined answer.

Right now it compares the result sets of the two queries using some FOR loops from PHP. It searches every column from the defined query and tries to find it in the input query;

Is there any way to check this on the oracle level?

Example : lets say we have the following table

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---

  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  7499            SALESMAN        7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
  7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
  7788 SCOTT      ANALYST         7566 13-JUL-87       3000                    20
  7839 KING       PRESIDENT            17-NOV-81       5000                    10
  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
  7876 ADAMS      CLERK           7788 13-JUL-87       1100                    20
  7900 JAMES      CLERK           7698 03-DEC-81        950                    30
  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
  7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

The defined answer :

 SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20;

The input answer:

SELECT * FROM EMP WHERE DEPTNO=20;

These two queries are more ore less equivalent in terms of the selected rows.

How can i check this fact ?

1. (That 2 queries are equivalent- in terms of rows)
2. (The input query may have extra columns)

What I've tried:

1. using the UNION/MINUS operators. (The problem is that when the queries have a different number of columns MINUS doesn't work).

Any suggestions?

Thanks in advance.

Answer 1

It depends to some extent on what information you are willing to rely on your application to provide, and what you hope to extract dynamically. It also depends on how you have in mind to pose the question to Oracle.

If the idea is that you will construct a query that incorporates the reference query and the input query and tells you whether they correspond, and if you are willing to rely on the application providing an explicit column list, and if one of the things you want to check is result set column labels, and if you do not care about column or row order, then you can do it like this:

WITH
  expected AS (
    -- select all the expected columns, plus a count
    SELECT ENAME, JOB, COUNT(*) AS count FROM (
      -- the standard query to test against
      SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20
    ) reference
    -- group by all the columns of the result
    GROUP BY ENAME, JOB
  ),
  observed AS (
    -- choose only the expected columns (Oracle will barf if any are not present),
    -- plus a count
    SELECT ENAME, JOB, COUNT(*) AS count FROM (
      -- the query whose results are to be tested
      SELECT * FROM EMP WHERE DEPTNO=20
    ) user_specified
    -- group by all the columns of the reference result
    GROUP BY ENAME, JOB
  )
SELECT COUNT(*) as differences
FROM (
    (
      SELECT * FROM expected
      MINUS
      SELECT * FROM observed
    )
    UNION ALL
    (
      SELECT * FROM observed
      MINUS
      SELECT * FROM expected
    )
  ) differing_rows

That's largely boilerplate. You plug in the whole reference query in one place, the whole input query in another, and the (same) expected column list in four separate places. The query result is a single row with a single column containing the value 0 if the query results exactly match for the columns of interest (ignoring column and row order), or a value greater than zero otherwise. In the event that either component query returns duplicate rows, the overall result tests whether the two return the same number of copies of each duplicate row.