After a discussion of which users were from Australia, I wrote my first SQL query to find out:

SELECT u.DisplayName'Display Name', u.Reputation'Rep', u.Location'Location'
FROM Users u
WHERE u.Location LIKE '%Australia%'

As always, please tell me the good, the bad, and the ugly.

The query can be found here

  • \$\begingroup\$ You should parameterize the query for generality, so that others may benefit from it. \$\endgroup\$ – 200_success Apr 17 '15 at 11:13
  • \$\begingroup\$ @200_success Done: meta.stackexchange.com/a/253987/245368 \$\endgroup\$ – user34073 Apr 21 '15 at 4:39

I don't like how you're specifying the column aliases. I expect a whitespace between the column and the alias.

I like that you're not specifying the optional AS keyword though - I find it only adds clutter when it's there.

Also I would have used [square brackets] instead of single quotes, and layout the field names on separate lines, like this:

     u.DisplayName [Display Name]
    ,u.Reputation [Rep]
    ,u.Location [Location]

That way you can easily add, reorder, or comment-out a column if you need to.

  • 1
    \$\begingroup\$ I'd throw an AS between the column name and the alias. I think it's a little easier to understand. I agree with the rest of this though. \$\endgroup\$ – PenutReaper Apr 16 '15 at 10:15
  • \$\begingroup\$ I prefer minimizing the number of meanings of keywords I use, whenever possible. AS has different semantics in, say, as CTE definition. \$\endgroup\$ – Mathieu Guindon Apr 16 '15 at 13:28
  • \$\begingroup\$ Now you mention it, I can see why you'd choose not too. Personally I'm not a big fan of CTE's in general and don't use them much, although that's neither here nor there, and so I completely forget about it in that context. It's just more proof that we really need an agreed upon SQL standard if you ask me, which would help a lot reviewing SQL too. But alas, there is none. \$\endgroup\$ – PenutReaper Apr 16 '15 at 14:58

You should use (if the SQL version wasn't T-SQL, which it is. Thanks @Millie Smith and @Vogel612)

lower(u.Location) LIKE '%australia%'

In case of lowercase writing of Australia.

I wouldn't really assign Reputation an alias, because it's actual title is fine.

You literally assign Location, to be Location, just without the u.

That's a tad unnecessary.

I thought perhaps people may use the country abbreviation, au so you could consider that, but other than that, the code is good.

  • 1
    \$\begingroup\$ Which SQL dialect is this? In T-SQL, LIKE is case-insensitive. Edit: It depends on the collation for T-SQL, though I assume most have it set to case-insensitive. \$\endgroup\$ – Millie Smith Apr 16 '15 at 6:15
  • 1
    \$\begingroup\$ @millie since this question is about the stackexchange data explorer, the dialect is T-SQL \$\endgroup\$ – Vogel612 Apr 16 '15 at 8:48

I see from your data that location can be say "Western Australia, Australia"

Note there is also: https://en.wikipedia.org/wiki/Australia,_Cuba

Strictly speaking asking for Australia would get you that and it would be correct (or not, depending on if you only wanted the country..).

[Australia is also a continent be I guess then for non-Australia-countries that text wouldn't be in your field..]

If you can control the table, you might want to have a separate field for country? If not:

lower(u.Location) LIKE '%australia'

or something better (in case you has a period afterwards or something..).

One more trivia: LIKE can use an index (depending on database and its cleverness = might be faster or not (by much)), except when starting with % that forces a (slower) sequential scan. This could also be an argument for having country in a separate field.

Even without % or if using a = instead of like, lower-function will also force a sequential scan. Databases with functional indexes allow you to make the right one avoiding that. Or the commonly used trick (but might just shift your work to the INSERT..):

Using codes:

where country = 'AU'
  • 4
    \$\begingroup\$ @Quill You'll sometimes see the Oceania combined with Australia deemed to be a single "continent", but very often Australia is considered to be a continent (for example, here). \$\endgroup\$ – David K Apr 16 '15 at 13:00

It might not seem appropriate in this case as there aren't any obvious variations on "Australia" (excluding typos), but this sort of filtering is often better achieved by normalising the data - using a separate look-up table for location, with a foreign key link in the user table.

I once had to do similar analysis on address data to extract records from USA and there were 47 different variations of the text field for country name ("USA", "U.S.A.", "United States", "US of America" etc etc).

  • \$\begingroup\$ The schema is provided by Stack Exchange, this is a SEDE query - see the Data Explorer \$\endgroup\$ – Mathieu Guindon Apr 16 '15 at 13:26
  • 2
    \$\begingroup\$ Yes, I realised that after posting, but the answer still applies to those people designing their own database for this type of query. \$\endgroup\$ – Peregrine Apr 16 '15 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy