Joining to Sub-queries in SQL

Library index card file drawers, just chilling

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.

Leave a Reply

Your email address will not be published. Required fields are marked *