Select only those rows that are in multiple result sets

Question

I have multiple SELECT statements that all return the same columns but may return different resultsets. Is there any way to select all rows that are in all resultsets on database level?

E.g.

|---------------------|------------------|---------|
|          ID         |        Name      |   Age   |
|---------------------|------------------|---------|
|          1          |       Paul       |   50    |
|          2          |       Peter      |   40    |
|          3          |       Frank      |   20    |
|          4          |       Pascal     |   60    |
|---------------------|------------------|---------|

SELECT 1
SELECT name FROM table WHERE age > 40
Result: Paul, Pascal

SELECT 2
SELECT name FROM table where name like 'P%'
Result: Paul, Peter, Pascal

SELECT 3 
SELECT name FROM table where id > 3
Result: Pascal

EDIT: This is a very simplified example of my problem. The statements can get very complex (joins over multiple tables), so a simple AND in the WHERE part is not the final solution.

The result should be Pascal. What I am looking for is something like a "reverse UNION".

Alternatively it would be possible to achieve that programatically (NodeJS), but I would like to avoid to iterate over all resultsets, because they might be quite huge.

Thanks in advance!

Neither Paul nor Thomas is in all result sets. Paul is only in #1 and #2 and Thomas is only in #1 and #3. So the overall result set should be empty. Or am I mistaken? — Thorsten Kettner, Feb 09 '20 at 21:44
Sorry, my fault. I changed "Thomas" to "Pascal" and fixed the resultset to keep it simple. :) — Christian Riese, Feb 09 '20 at 21:50
Complex? Joins? Don't the selected columns und rows stem from the same table(s)? What you are looking for is `INTERSECT` in standard SQL by the way, but MySQL doesn't support it. — Thorsten Kettner, Feb 09 '20 at 21:57
That is EXACTLY what I want! Very sad, that MySQL does not support that. :( — Christian Riese, Feb 09 '20 at 22:18
Anyway, as you are talking about three queries resulting in the same rows, it is likely that the data you are selecting stems from the same set of tables. Only the conditions are different. Make sure, your conditions are all in the `WHERE` clause, not in the joins. Especially, you should use `[NOT] IN|EXISTS` and not joins to ensure existence. And then at last just combine those conditions with `AND` as has been suggested by GMB. — Thorsten Kettner, Feb 09 '20 at 22:23
As I stated in GMBs comment, the resultsets must not necessarily come from the same tables. I should have make that clearer in my initial question. Intersect is exactly what I need ("reverse Union"). I will probably try the `IN` approach an MySQL. — Christian Riese, Feb 09 '20 at 22:24
Okay, it is somewhat rare to expect the exact same data, but from different tables. The only such situation that comes to mind are history tables mimicking the original tables. Maybe you should just show your real queries here to get better help. Well, maybe just accept one of the answers and make a new request then. — Thorsten Kettner, Feb 09 '20 at 22:27

score 2 · Answer 1 · answered Feb 09 '20 at 21:39

2

Is there any way to select all rows that are in all resultsets?

You seem to want and:

select name 
from table 
where age > 40 and name like 'P%' and id < 3

answered Feb 09 '20 at 21:39

GMB

216,147
25
84
135

I edited my question to clarify the problem a bit more. The statements can get very complex, so that an "AND" is not possible or not wanted. – Christian Riese Feb 09 '20 at 21:52
@ChristianRiese: your sample data is not consistent: Pascal has id 4, which is above 3, so the third query does not return it. How is `where` not a solution for your requirement? It returns the record that satisfy *all the conditions*, which seem to be what you want. – GMB Feb 09 '20 at 21:57
@ChristianRiese . . . This answers the question that you have asked here. If your question is not representative of the problem you actually have, then ask a NEW question and provide a better example -- this should help you understand why the example you provided may not be good for your ultimate purpose. – Gordon Linoff Feb 09 '20 at 22:14
Where is not the answer to my questions because the results of the the selects must not necessarily come from the same tables. The only precondition is, that the resultsets look the same. Maybe I should clarify that in my question. – Christian Riese Feb 09 '20 at 22:16

score 1 · Answer 2 · answered Feb 09 '20 at 21:57

If using AND between the WHERE conditions is not possible, you could use multiple IN expressions on subqueries using your initial queries.

SELECT name
FROM table
WHERE id IN (SELECT id FROM table WHERE age > 40)
  AND id IN (SELECT id FROM table where name like 'P%')
  AND id IN (SELECT id FROM table where id < 3)

score 0 · Answer 3 · answered Feb 09 '20 at 22:16

If you have different result sets and you want to see the intersection, you can use join:

select q1.id
from (<query 1>) q1 join
     (<query 2>) q2
     on q1.id = q2.id join
     (<query 3>) q3
     on q1.id = q3.id;

That said, I think GMB has the most concise answer to the question that you actually asked.

score 0 · Answer 4 · answered Feb 09 '20 at 22:17

If your statements are complex, what you could do is to use a procedure where each of the statements put the matching id's into a temp table. Then select those rows where id's match the number of statements. This will also most likely be more efficient than one huge query with all complex statements combined into one.

create procedure sp_match_all()
begin

drop temporary table if exists match_tmp;
create temporary table match_tmp (
id int
);

insert into match_tmp
SELECT id FROM table WHERE age > 40;

insert into match_tmp
SELECT id FROM table where name like 'P%';

insert into match_tmp
SELECT id FROM table where id < 3;

select t.name
from table t
  join (
    select id
    from match_tmp
    group by id
    having count(*)=3
  ) q on q.id=t.id;

drop temporary table match_tmp;

end

Select only those rows that are in multiple result sets

4 Answers4