Edward Loveall

Cool Things You Can Do with SELECT

I’ve been doing a lot of SQL at work. Some of the queries are quite large (many hundreds of lines) and long-running. If the query has a problem, it can be difficult to break it down to debug.

One thing that continues to come in handy is using SELECT by itself.

Operations

For example, if I want to understand why the string '10–20' isn’t being captured by my [0-9]+-[0-9]+ SIMILAR TO regular expression, I can use SELECT to try it out without running the whole query:

SELECT '10–20' SIMILAR TO '[0-9]+-[0-9]+'; -- FALSE

It’s instant and allows me to continue to break it apart to find that the separator is an em dash – not an en dash - and adjust the regular expression to include either:

SELECT '10–20' SIMILAR TO '[0-9]+[-–][0-9]+'; -- TRUE

Connection test

Not sure if you’re still connected to that remote database? SELECT 1 will tell you and is both faster to type and faster to run than something like SELECT * FROM users.

Functions

SELECT also returns the result of functions. Things like the current_timestamp, the database version(), or the weekday of a specific date:

SELECT current_timestamp;

SELECT version();

SELECT EXTRACT(DOW FROM TIMESTAMP '1999-01-01'); -- 5, Friday

Note: I don’t understand why current_timestamp doesn’t want parenthesis and version() does and didn’t feel like diving into that. If you do, let me know!

Static Data

Finally, SELECT can create data for you. Admittedly this isn’t a use case I have often. But I can imagine some case where you need to UNION ALL two queries together and the columns need to match. In that case you can create some fake columns:

SELECT NULL AS user_id, 'guest' as username;

Or maybe you need a couple of rows for a JOIN:

SELECT 1 AS user_id
UNION ALL
SELECT 2 AS user_id;

Diving into FROM is beyond the scope of this post, but I also should mention that if you do want a lot of data like this, you can create fake rows with FROM:

SELECT
  *
FROM (
  VALUES
  (1, 'chicken'),
  (2, 'bacon'),
  (3, 'ranch')
) AS users (id, username)

Anyway, don’t forget about SELECT. It’s more than just a tool to specify which rows you want from a table.