Chris Sunami is the lead Web and Internet Applications Designer for NMP.
Databases are so much more than online spreadsheets. They are at the root of nearly every piece of dynamic content found on the web. Blogs, message boards and wikis can all be run from databases. Even some of the static pages on our own NMP website float invisibly and seamlessly over the foundation of a SQL-based* database.
Today’s tip is about SQL subqueries. With enough server-side programming, it’s possible to create powerful applications that never use a subquery at all. But it’s far faster, more secure and more elegant to take advantage of this useful tool built into SQL at a foundational level.
In sum, anywhere you would ordinarily place a tablename in a SQL query, you can replace that table with a second SQL query, thus creating a new virtual table that has only the specific info you asked for.
Here’s how it works:
Let’s start with an ordinary query involving two tables:
Q1:SELECT table1.field1, table1.field2, table2.field3 FROM table1, table2 WHERE table1.field4=table2.field4"
Now let’s look at another generic query:
Q2:SELECT table3.field2, table4.field3 FROM table3, table4 WHERE table3.field5=table4.field5
With subqueries, we can substitute a query like Q2 in place of one of the tables in Q1 –like so:
Q3:SELECT table1.field1, table1.field2, alias.field3 FROM table1, (SELECT table3.field2, table4.field3 FROM table3, table4 WHERE table3.field5=table4.field5) AS alias WHERE table1.field4=alias.field4
What we did was enclose the subquery in parentheses, and then give it a name (“alias”) by using the “AS” key word. Then, anywhere we needed to refer to that subquery, we did so by using the name we gave it. Simple, but also very powerful.
* The SQL family of databases includes MS SQL, MySQL, PostGreSQL and many others, all based on the SQL database language.