Cookie Notice

As far as I know, and as far as I remember, nothing in this page does anything with Cookies.

2012/07/09

Petdance says if you have SELECT * in your code, you have a bug

Or, he retweeted it. I think. He knows where to yell at me if I got that wrong.

I don't really agree, except maybe the memory hit when you get tons more data than you expected or need, but the case I'm talking about here is a structured database dump. I'm proud of this query, and I'll expand on it.

        SELECT *
        FROM  sequence_sample
        LEFT JOIN accessions
            ON  sequence_sample.accession_id = accessions.accession_id
        LEFT JOIN requests
            ON  accessions.request_id        = requests.request_id 
        WHERE run_id = ?

A sample is an accession, except that a sample is an accession associated with a run, and that accession can be associated with many runs but only one request. I could see adding several joins to bring in controlled vocabularies, where, for example, the library types come in.

I've worked with SQL for a while, but only now am I integrating the joy of joins. I can thank Coding Horror for his excellent explanation on what the different joins mean, and Learning SQL for the use of ON to specify  the JOIN joins to distinguish it from WHERE which brings in the unique constraints