Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 at 0:22
1  
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 at 2: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 at 2:35
add comment

5 Answers 5

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
)
share|improve this answer
    
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 at 9:00
    
Indeed, this query work :D. –  Deathunt Jan 10 at 14:46
add comment

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)
share|improve this answer
3  
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 9 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 at 14:18
    
ignore that request, I see my misunderstanding. –  Andrew Bickerton Jan 10 at 14:22
add comment

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.

share|improve this answer
1  
This will return different results than the Op's query. –  ypercube Jan 10 at 11:22
    
@ypercube - Noted. Thanks. –  Pierre Jan 10 at 12:34
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.