Subquery Table Aliases

I have a query that has a number of subqueries and some of those subqueries hit the same tables

Each time I've given the tables in the subqueries their own unique alias to avoid tainting the query and it does make reading the debug output a bit easier

But...
Is it strictly necessary?
I'm thinking that the subquery should isolate the additional tables called

 

In a platform I've written we have a reporting tool - always provides a list of people.
But the user might want to only see those going to an event or whose subscription will lapse next month but because it's a tool that builds the query rather than one written from scratch I have needed to use some subqueries. I build up a few arrays for tables, where, order and then implode them to build the final query once I've run through the user criteria.

So, lets say we're looking for subscriptions that are lapsing
I'll look for a person's newest subscription and see if it's end date is after this month
and then I'll look for a person's newest subscription and see if it's end date is before the end of next month

Both of these checks are subqueries on the same table because of the need to be looking at the newest record.

 

You don't *have* to have an alias, but it's good practice in order to keep track of things in more complicated queries. For example:

SELECT COUNT(*)
FROM og_locations
WHERE location_id IN (
    SELECT location_id
    FROM og_locations
    WHERE location_name LIKE 'A%'
)

Code (SQL):

This works fine (I know it's a silly use for a subquery, just making an example).

 

So, if you have two subqueries that both use og_locations will the subquery encapsulate the table so that they don't interfere with each other?

 

Yeah... it does become a problem if you try to use something from your subquery in the main query because it doesn't know if you want it from the primary query or the subquery. But you should get a SQL error about that (for example how it doesn't know which table to pull from if you are trying to output a joined column with the same name in both tables without calling out which table you want it from). You *should* always get an error if there's any confusion in the query on where the data should come from (tables, queries, subqueries, etc.).

Either way, it's still good practice to alias them.

 

all my subqueries are plain exists type of thing
I've tended to give the aliases within a subquery a common name that relates to the purpose of the subquery so that if I access, say, finances several times it'll be clear why.

It's been a fun project, making querying simpler for the user and easily extendable without having to do a massive rewrite each time.