Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This question already has an answer here:

Suppose I was writing an address book application where the details of each contact were stored in an SQL database.

A user wants to know all the details for a person whose name is "Bob [Someone]".

In such a case, would writing a query like this be okay?

SELECT *
FROM contacts
WHERE name LIKE 'Bob %';

I'm confused because my professor said to avoid using the * wildcard but I can't think of an alternative way of writing it.

Is it okay to use the wildcard if I run this query on a seperate thread.

Thanks for your help.

share|improve this question

marked as duplicate by Yannis Rizos Apr 6 at 16:46

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2  
@W.K.S: I think this is not a place where ppl should come, because they are to lazy to use Google. Besides, even if it wasn't such a simple question, is off-topic here. –  vartec May 4 '12 at 14:56
add comment

6 Answers

up vote 13 down vote accepted

The * is a shorthand for selecting all columns from the result set; the alternative way of writing this is to spell out all the columns you expect in the contacts table, e.g. SELECT id, name, email FROM contacts WHERE name LIKE 'Bob%'.

In layman's terms, SELECT name, email FROM ... means "tell me the names and e-mail addresses of ...", while SELECT * FROM ... means "tell me everything you know about ...".

The reason people discourage SELECT * are twofold: first of all, if you use it in production code, and the database changes for whichever reasons, your column ordering will be off, and you will be fetching results into the wrong variables. If you specify columns explicitly, you can rely on the correct number and ordering of result columns, and the query will fail loudly if the columns you specify do not match what was found in the database.

Another reason is performance. SELECT * means that the DBMS has to perform an extra lookup to get the list of columns. It also means that you haven't thought about what you really need, so chances are you'll be fetching more data than you need, which, considering the typical performance bottlenecks in web applications, is something you really want to avoid.

Finally; threads have absolutely nothing to do with this. Nothing at all. I wonder where you got that notion; it looks like you're still quite confused about SQL syntax.

TL;DR: SELECT * is OK for queries you fire manually, but you should avoid it in production code, because it can produce unexpected results and may lead to inefficient queries.

share|improve this answer
    
Thanks. I mentioned threads because my professor mentioned that * wildcard impeded performance. I thought if that's the only problem, perhaps threads could be a work-around. Your answer cleared that misconcept :) –  W.K.S May 4 '12 at 14:54
1  
Threads do not magically increase performance. They can be used to make independent tasks run faster by utilizing more CPU cores, to keep a UI responsive while the computer runs expensive calculations in the background, and to run "sparse" processes in parallel (e.g. waiting for network resources). In this context, if you have a long-running query, you might consider running it in a separate thread so that the UI remains responsive in the meantime, but the query itself won't run any faster. –  tdammers May 4 '12 at 15:02
1  
The one exception to the rule is when using aggrigation functions. count(*) will count the number of rows in a table. While count(XXX) will count the number of rows that are non NULL in the column XXX. It is advisable to pick * here (even over a key) as the DB may change in production and the count(*) will always retain its meaning. –  Loki Astari May 4 '12 at 15:58
    
Threads certainly can make a query run faster if you put together a query that results in a parallel execution plan, but there can be drawbacks, as well. sqlblog.com/blogs/paul_white/archive/2011/12/23/…, searchitchannel.techtarget.com/feature/…, technet.microsoft.com/en-us/library/ms178065(v=sql.105).aspx. –  Craig Apr 10 at 4:57
add comment

Think of the * as a column selector rather than a wildcard. The reason why your professor said that was because selecting all columns is an inefficient way of getting data, since you'll not likely need all the information that will be retrieved.

Contrarily, the '%' is a wildcard, and I think that's what you're confusing with '*'. In your query, you're using '%' to specify anyone with name starting with 'Bob '. Note that if you had used '*' for this, you would have been looking for anyone whose name is exactly 'Bob *'.

share|improve this answer
add comment

Assuming that the whole name is in one field, and you don't have first and last separated into two columns, then your base query is fine.

If you can't use the * wild card, you can get each field individually, like this:

select name, phone, address from contacts where name like 'Bob%';
share|improve this answer
add comment

The alternative is to name all the details in your SELECT clause explicitly - or better, all those details that you actually want.

The use of * is discouraged for two reasons:

  • it has to retrieve more data than a focused query for the data that you actually need, so it can waste resources.
  • it could encourage the client programmer to expect the fields to be returned in a particular order, and with a particular cardinality. If you then extract the 'first' field from the result set and display it, the result will look correct, until someone adds or changes the column definition of the underlying table, whereupon the previously working code suddenly breaks, with neither side feeling responsible.

Therefore, best practice is to explicitly select those columns that are actually wanted, and retrieve them by name rather than by position.

share|improve this answer
add comment
SELECT Name, Birthday, Adress
FROM contacts
WHERE name LIKE 'Bob %';
share|improve this answer
add comment

The "*" in this case is pulling in all fields in the table. It has nothing to do with the search criteria "Bob %". In almost all cases you should list the fields you want returned explicitly.

share|improve this answer
add comment

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