In writing some test queries for my Twitter-ish SQLite database, I came across the idea of nested SQL statements. I noticed a limitation pretty early on, where I wanted to save the result of a query and use it in another one. To do this, you just put brackets around a valid
select statement, and get rid of the semicolon.
For example: say you wanted to get the user ID of someone named 'barry', and insert it into the Post table along with the post content "Barry's Post":
insert into Post values ( null, -- The Post ID, which SQLite auto-inserts for us. (select user_id from User where username = 'barry'), 'Barry''s Post', null -- The timestamp, which we'll add to the real thing. );
Line 3, starting with
(select..., is an example of this kind of nested query. And it really isn't too hard to understand! SQLite evalues the result of the statement inside the parens, and then passes it on to the outer
Here, we're making the assumption that the username 'barry' is unique; it's easy enough to enforce this by modifying the User scheme such that
username is a unique field. We also can't return more than one column in the inner statement: luckily, this isn't a problem in a relational database, as we can return the primary key of the table, and retrieve any extra information from there in other queries. As far as I remember, this is called normalization, and makes sure we don't duplicate data.
As a quick note: the double-apostrophe on line 4 isn't a typo. It's the way SQLite escapes single-quotes, as the C-style backslash escape character syntax isn't valid SQL.