SQL subqueries what has the best performance

Question

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?

score 2 · Answer 1 · answered Aug 23 '12 at 12:23

2

For performance point of view use this query

 select class 
 FROM Ships 
 join outcomes on ships.name=outcomes.ship
 and result like 'sunk'

answered Aug 23 '12 at 12:23

Asif

2,657
19
25

if he's using SQL-Server this could be less performant. In MySQL is the same – Gonzalo.- Aug 23 '12 at 12:27

score 1 · Accepted Answer · answered Aug 23 '12 at 12:25

1

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’
       )

answered Aug 23 '12 at 12:25

Robert

25,425
8
67
81

score 0 · Answer 3 · answered Aug 23 '12 at 12:21

0

In this case, you need to state the column you are selecting.

I would recommend a join instead

 seLECT class 
 FROM Ships 
     inner join outcomes
     on ships.name=outcomes.ship
    WHERE result = ’sunk’

answered Aug 23 '12 at 12:21

podiluska

50,950
7
98
104

Curtis · Answer 4 · 2012-08-23T12:28:59.843

You would need to select a single column, as you have done in your SQL example.

Its also worth noting that wildcard * is bad for performance as the server will then need to do some extra work to work out what all the columns are, as you have not explicitly stated.

If you want to include more than one column to be compared in the IN then I would suggest using a UNION in your sub-query:

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

score 0 · Answer 5 · answered Aug 23 '12 at 12:24

You are using In, so you need only one row to compare it to name. In this particular case, you can use a Join as an alternative. In MySQL are equally performant, but in SQL-Server In is more performant than Join. So, you you will have to return only one row in this case.

In case you use a subquery in a inner join, you could use *, but for readability is better to return the fields you will use. This example, will be

select (fields..)
from yourTable
inner join (select fields from yourTable2) T2 on ConditionOfJoining

select (fields..)
from yourTable
inner join (select * from yourTable2) T2 on ConditionOfJoining

score 0 · Answer 6 · answered Aug 23 '12 at 12:25

0

You should always try to avoid to use *. Even better would be never to use the *.

But in your query you MUST state the column.

answered Aug 23 '12 at 12:25

KouryouChairudo

31
3

I agree with Asif below select class FROM Ships join outcomes on ships.name=outcomes.ship and result like 'sunk' But your database design might even be better when you have a foreign key in your "outcomes" table that is linked to the id of your "ships" table, instead of using the name. (Uses less storage, and prevents typos) SELECT class FROM Ships INNER JOIN outcomes ON ships.Id = outcomes.shipId and result = ’sunk’ – KouryouChairudo Aug 23 '12 at 12:26

SQL subqueries what has the best performance

6 Answers6