More efficient way to write multiple "AND"?

Question

Anyone know a way to write this in a different manner ?

SELECT tableA.NO
FROM tableA
WHERE tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =62)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =50)
  AND tableA.NO IN (SELECT GRI_NO FROM tableBB WHERE GRO_NO =70)

I have many more AND to add and it become quite a long query. Does an equivalent of WHERE GRO_NO IN (62, 50, 70) exist for AND?

Edit below:

The equivalence of sampleA1 is sampleA2 for the OR keyword.

sampleA1: WHERE GRO_NO IN (62, 50, 70)

sampleA2: WHERE GRO_NO =62 OR GRO_NO =50 OR GRO_NO =70)

Does an equivalent of sampleB2 exist for the AND keyword ?

sampleB2:WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)

Edit2: I'v remove performance tag since it was more related to clarity / syntax / shorter sql than DB performance.

Its unclear what semantics you are desiring here. Do you want the column tableA.NO to be in BOTH the first SELECT output AND in the next one too? — Thomas Kejser, Jan 10 '14 at 00:22
This type of problem is called relational division. You can see a similar question at dba.se: What is the name of this type of query, and what is an efficient example? and one at SO with many (more than 10!) ways to solve it: How to filter SQL results in a has-many-through relation — ypercubeᵀᴹ, Jan 10 '14 at 02:33
Efficiency will vary across the different ways with the DBMS, the sizes/distributions of the tables, the indexes and several other parameters. Longer query (in characters) does not mean slower. — ypercubeᵀᴹ, Jan 10 '14 at 02:35

score 4 · Answer 1 · answered Jan 09 '14 at 17:41

4

Not sure if that's is faster than what you have, but you can try

SELECT tableA.NO
FROM tableA
WHERE tableA.NO
IN 
(
   SELECT GRI_NO FROM tableBB WHERE GRO_NO IN (62,50,70) 
   GROUP BY GRI_NO
   HAVING COUNT(DISTINCT GRO_NO) =3
)

answered Jan 09 '14 at 17:41

a1ex07

9,000
3
24
40

He doesn't look for performancewise efficiency, still +1 because you're the only one so far who has a query which actually gets the same result as the original. – tombom Jan 10 '14 at 09:00
Indeed, this query work :D. – Deathunt Jan 10 '14 at 14:46

Andrew Bickerton · Answer 2 · 2014-01-10T14:33:34.990

I would recommend doing a join checked with group by and having or exists checks

JOIN

SELECT tableA.NO
FROM tableBB 
    JOIN tableA ON tableA.NO = tableBB.GRI_NO
WHERE 
    tableBB.GRO_NO IN (62,50,70)
GROUP BY tableA.NO
HAVING COUNT(DISTINCT tableBB.GRO_NO) = 3 /* specify the count of GRO_NO's required */

EXISTS check could be used in line with existing AND check (in OP question)

SELECT tableA.NO
FROM tableA
WHERE 
    EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 62)
    AND EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 50)
    AND EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 70)

Depending on the distribution of your data you should find one or the other will run faster.

If you needed to expand the criteria of a particular GRO_NO ie: for 70, check that IsActive=1, you could do:

JOIN

SELECT tableA.NO
FROM tableBB 
    JOIN tableA ON tableA.NO = tableBB.GRI_NO
WHERE 
    tableBB.GRO_NO IN (62,50,70)
    /* GRO_NO specific clause */
    AND ((tableBB.GRO_NO = 70 AND tableBB.IsActive = 1) OR tableBB.GRO_NO != 70)
GROUP BY tableA.NO
HAVING COUNT(DISTINCT tableBB.GRO_NO) = 3 /* specify the count of GRO_NO's required */

EXISTS

SELECT tableA.NO
FROM tableA
WHERE 
    EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 62)
    AND EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 50)
    AND EXISTS (SELECT 'NO is in tableBB' FROM tableBB 
            WHERE tableA.NO = tableBB.GRI_NO AND tableBB.GRO_NO = 70 
                 AND tableBB.IsActive = 1)

tblBB.GRO_NO IN (62,50,70) --is the same than:
tblBB.GRO_NO = 62 OR tblBB.GRO_NO = 50 OR tblBB.GRO_NO = 70 --But don't give the same result than the original query — Deathunt, Jan 09 '14 at 18:48
Depending on the query engine and statistics on the table you can get a different query plan when using IN (62,50,70) vs tblBB.GRO_NO = 62 OR tblBB.GRO_NO = 50 OR tblBB.GRO_NO = 70. But the main benefit is concise behaviour. Can you expand on your comment that you don't get the same results as the previous query (please add to your question)? I would hazard that my addition of DISTINCT is where the behaviour is different.. — Andrew Bickerton, Jan 10 '14 at 14:18

score 1 · Answer 3 · answered Jan 10 '14 at 14:27

An alternate way to write:
WHERE GRO_NO =62 OR GRO_NO =50 OR GRO_NO =70)

in a COMPACT syntax is:
WHERE GRO_NO IN (62,50,70)

But as far as I know SQL, it seem there no alternative to write:
WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)
in a COMPACT systax

score 0 · Answer 4 · answered Jan 10 '14 at 08:44

0

Better option would be to use a single tableBB select, with an IN on the GRO_NO column

i.e.

...WHERE tableA.NO IN (select GRI_NO from tableBB where GRO_NO in (62,50,70))

,seeing as you have multiple selects for tableBB.

answered Jan 10 '14 at 08:44

Pierre

63
1
6

1

This will return different results than the Op's query. – ypercubeᵀᴹ Jan 10 '14 at 11:22

score 0 · Answer 5 · answered Jan 10 '14 at 19:12

0

You ask

Does an equivalent of sampleB2 exist for the AND keyword ?

sampleB2:WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)

Would it be false always? If so, you can write WHERE GRO_NO <> GRO_NO

answered Jan 10 '14 at 19:12

msi77

1,135
1
7
9

More efficient way to write multiple "AND"?

Edit below:

Edit2: I'v remove performance tag since it was more related to clarity / syntax / shorter sql than DB performance.

5 Answers5

You ask

sampleB2:WHERE GRO_NO =62 AND GRO_NO =50 AND GRO_NO =70)