1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Subquery Table Aliases

Discussion in 'MySQL' started by sarahk, Jun 6, 2017.

  1. #1
    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
    SEMrush
     
    sarahk, Jun 6, 2017 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,645
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #2
    Can you post an example? I'd say it depends, but segmentation is typically a good thing. But, too difficult to say without seeing how the query is organized and exactly what you're trying to accomplish.
     
    jestep, Jun 8, 2017 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    24,547
    Likes Received:
    3,308
    Best Answers:
    95
    Trophy Points:
    615
    #3
    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.
     
    sarahk, Jun 8, 2017 IP
  4. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,220
    Likes Received:
    2,589
    Best Answers:
    458
    Trophy Points:
    710
    Digital Goods:
    29
    #4
    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).
     
    digitalpoint, Jun 8, 2017 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    24,547
    Likes Received:
    3,308
    Best Answers:
    95
    Trophy Points:
    615
    #5
    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?
     
    sarahk, Jun 8, 2017 IP
  6. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,220
    Likes Received:
    2,589
    Best Answers:
    458
    Trophy Points:
    710
    Digital Goods:
    29
    #6
    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. :)
     
    digitalpoint, Jun 8, 2017 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    24,547
    Likes Received:
    3,308
    Best Answers:
    95
    Trophy Points:
    615
    #7
    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.
     
    sarahk, Jun 8, 2017 IP