Search code examples
sqlperformancesubqueryexistsin-clause

SQL subqueries what has the best performance

I'm having a question about this SQL query:

 SELECT class
 FROM Ships
 WHERE name IN (SELECT ship
                FROM Outcomes
                WHERE result = ’sunk’);

Can I write in the subquery SELECT * From Outcomes or do I always need to select a row?

And what query has the best performance then?

Solution

  • You can't put * in this subquery. The best way to do it is:

    SELECT class
  FROM Ships s
 WHERE exists
     (
        select 1 
          from Outcomes o
        where s.name = o.ship
          and result = ’sunk’
       )