Using EXIST in SQL

Question

How can I modify this query to use exists?

SELECT allele.allele as allele,site.allele_id as allele_id, site.genome_site_id as genome_site_id, popl.popl_id as popl_id,popl.frequency as frequency 
FROM kref.allele as allele 
    join kref.genome_site_allele as site using (allele_id) 
    LEFT JOIN kref.genome_site_allele_popl as popl USING (ref_genome_id, allelism_start, allelism_end, chrom_id, allele_id) 
WHERE ref_genome_id= 2 AND allelism_start=25710014 AND allelism_end=25710015 
    AND chrom_id ='22' 
    AND site.allele_id IN (
        SELECT allele_id 
        from kref.allele 
        where allele IN ('-','C' )
    )

can you eleborate on what is your goal just to check if you get any results or something else? — Jester
– Jester, Commented Nov 8, 2012 at 15:14

AndreKR · Accepted Answer · 2012-11-08 15:20:24Z

1

For whatever reason you might want that, here's a semantically equivalent query using EXISTS:

SELECT allele.allele as allele,site.allele_id as allele_id,
       site.genome_site_id as genome_site_id, popl.popl_id as popl_id,
       popl.frequency as frequency
  FROM kref.allele as allele
  JOIN kref.genome_site_allele as site using (allele_id)
  LEFT JOIN kref.genome_site_allele_popl as popl
      USING (ref_genome_id, allelism_start, allelism_end, chrom_id, allele_id)
 WHERE ref_genome_id= 2 
   AND allelism_start=25710014
   AND allelism_end=25710015
   AND chrom_id ='22'
   AND EXISTS (
     SELECT TRUE
     FROM kref.allele
     WHERE allele IN ('-','C')
     AND allele_id = site.allele_id
   )

edited Nov 8, 2012 at 15:20

answered Nov 8, 2012 at 15:15

AndreKR

34k21 gold badges120 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using EXIST in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related