Set intersection in MySQL: a clean way

Question

I want to fetch entities which satisfy all n constraints I give them.

OR operations can be performed by UNION. I wouldnt have asked this question if MySQL supported INTERSECT.

I have entities in the table subject and their attributes in *subject_attribute*.

I see the only way for AND operations as nested queries:

SELECT id
FROM subject_attribute
WHERE attribute = 'des_sen'
AND numerical_value >= 2.0
AND id
IN (
   SELECT id
   FROM subject_attribute
   WHERE attribute = 'tough'
   AND numerical_value >= 3.5
)

This means: " fetch entities which satisfy the lowest subquery, then eliminate those who satisfy a higher query" and so on.

---

rows(condn x) AND rows(condn y) AND rows(cond z) <--ideal
rows(condn x:rows(cond y:rows(cond z))) <-- I am stuck here

I prefer linearly chaining the conditions instead of nesting them as I want to

1. compose queries programatically
2. debug them better

---

My question: Given n individual queries, how do I AND them cleanly and linearly in MySQL?

Not by using nested queries or stored procedures.

Please note the part about individual queries.

---

Update: Jonathan Leffler answered it right. Mark Bannister's answer is much simpler (but I made some bad decisions). Please refer my answer if you are still confused on joins.

Answer 1

Assuming that each separate query is running against the same table, and that each of them is accessing different values of attribute, the simplest way of intersecting all such queries is of the form:

SELECT id
FROM subject_attribute
WHERE (attribute = 'des_sen' AND numerical_value >= 2.0) or
      (attribute = 'tough'   AND numerical_value >= 3.5) or
...
group by id
having count(distinct attribute) = N;

- where N is the number of attribute-numerical_value condition pairs.

Answer 2

In case anybody is confused how to concatenate several queries, here is a simplified representation:

SELECT result1.id
FROM (
<query #1>
) AS result1

INNER JOIN (
<query #2>
) AS result2
ON result1.id = result2.id

INNER JOIN (
<query #3>
) AS result3
ON result1.id = result3.id

where < query #1> would be

SELECT id
FROM subject_attribute
WHERE attribute = 'des_sen'
AND numerical_value >= 2.0

---

It's called a self-join, joining a table to itself. Here result1, result2 and result3 are aliases of the same table.

Answer 3

Isn't an INNER JOIN (or simply JOIN) the intersection you need? Assuming you make the join over the relevant common columns.

Hence:

SELECT s1.id
  FROM (SELECT id
          FROM subject_attribute
         WHERE attribute = 'des_sen'
           AND numerical_value >= 2.0
       ) AS s1
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'tough'
          AND numerical_value >= 3.5
       ) AS s2
    ON s1.id = s2.id

This extends to N queries (N > 2) cleanly and linearly.

---

Please explain how you extend it.

SELECT s1.id
  FROM (SELECT id
          FROM subject_attribute
         WHERE attribute = 'des_sen'
           AND numerical_value >= 2.0
       ) AS s1
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'tough'
          AND numerical_value >= 3.5
       ) AS s2
    ON s1.id = s2.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'though'
          AND numerical_value = 14
       ) AS s3
    ON s1.id = s3.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'through'
          AND numerical_value != 45
       ) AS s4
    ON s1.id = s4.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'plough'
          AND numerical_value < 9
       ) AS s5
    ON s1.id = s5.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'cough'
          AND numerical_value < 5
       ) AS s6
    ON s1.id = s6.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'bucolic'
          AND numerical_value >= 3.5
       ) AS s7
    ON s1.id = s7.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'set'
          AND numerical_value BETWEEN 0.23 AND 3.0
       ) AS s8
    ON s1.id = s8.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'intelligent'
          AND numerical_value >= 0.001
       ) AS s9
    ON s1.id = s9.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'anal-retentive'
          AND numerical_value < 7
       ) AS s10
    ON s1.id = s10.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'magnificent'
          AND numerical_value = 35
       ) AS s11
    ON s1.id = s11.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'quantum'
          AND numerical_value >= 55
       ) AS s12
    ON s1.id = s12.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'thoughtfulness'
          AND numerical_value >= 350.237
       ) AS s13
    ON s1.id = s13.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'calamity'
          AND numerical_value = 3.0
       ) AS s14
    ON s1.id = s14.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'pink'
          AND numerical_value > 0.5
       ) AS s15
    ON s1.id = s15.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'cornucopia'
          AND numerical_value BETWEEN 1 AND 12
       ) AS s16
    ON s1.id = s16.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'maudlin'
          AND numerical_value < 3.625
       ) AS s17
    ON s1.id = s17.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'triad'
          AND numerical_value >= 1.723
       ) AS s18
    ON s1.id = s18.id
  JOIN (SELECT id
          FROM subject_attribute
         WHERE attribute = 'ambient'
          AND numerical_value >= 3.1
       ) AS s19
    ON s1.id = s19.id