6

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.

W.K.S
  • 236
  • 3
  • 11
  • 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 04 '12 at 14:56

6 Answers6

16

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.

tdammers
  • 52,406
  • 14
  • 106
  • 154
  • 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 04 '12 at 14:54
  • 2
    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 04 '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. – Martin York May 04 '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. http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx, http://searchitchannel.techtarget.com/feature/Parallel-processing-Using-parallel-SQL-effectively, http://technet.microsoft.com/en-us/library/ms178065(v=sql.105).aspx. – Craig Tullis Apr 10 '14 at 04:57
9

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.

Kilian Foth
  • 107,706
  • 45
  • 295
  • 310
0

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.

Jaydee
  • 2,667
  • 1
  • 18
  • 17
0

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%';
Alan Delimon
  • 440
  • 2
  • 6
0

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 *'.

Neil
  • 22,670
  • 45
  • 76
-1
SELECT Name, Birthday, Adress
FROM contacts
WHERE name LIKE 'Bob %';
Pieter B
  • 12,867
  • 1
  • 40
  • 65