Today I learned a nice trick on how to take advantage of sub-queries and aliasing the result to be used again in the same query. This is a terribly basic example of how to use sub-queries for more efficient use of SQL.
All I’m going to do is write what a query could look like when using this technique. Let’s say you’ve got the following incredibly simple table:
app_user
id, email, age, gender, state
There’s no rhyme or reason for that example table, but it works nicely for this purpose. So, if we want to join back to the current table it can come at a cost. By writing a sub-query we can reduce of the amount of data that is queried and returned. It might look something like this:
SELECT
app_user.*,
sub_app_user.state
FROM
(SELECT
*
FROM app_user) AS sub_app_user
JOIN
app_user
ON
app_user.state = sub_app_user.state;
Again, there’s absolutely no reason for this table. But, I wanted to highlight that you now use the sub-queried value just like you would another table. So, to access the “table” value inside of the “sub_app_user” table that was created you simply use your alias, sub_app_user, and then do a dot notation to state.
This is used all over the place and is very helpful.