Basic MySql query question

Discussion in 'Programming & Software Development' started by Gunna, May 23, 2019.

  1. Gunna

    Gunna Member

    Joined:
    Dec 25, 2001
    Messages:
    7,048
    Location:
    Brisbane
    Ignoring the variable's, I cannot work out why this code has U.%field name% in the query. The query works but trying to understand the purpose of the stand alone 'U' as it would work without it:

    SELECT
    U.USR_UID, concat(U.USR_FIRSTNAME,' ' ,U.USR_LASTNAME)
    FROM
    USERS U
    WHERE
    U.DEP_UID = @@departmentUid
    AND U.USR_UID <> @@QPR_MGRHID
    AND U.USR_STATUS = 'ACTIVE';
     
  2. RnR

    RnR Member

    Joined:
    Oct 9, 2002
    Messages:
    13,012
    Location:
    Brisbane
    I think its just habit. When you are banging out the code, muscle memory is hard to override.
     
  3. OP
    OP
    Gunna

    Gunna Member

    Joined:
    Dec 25, 2001
    Messages:
    7,048
    Location:
    Brisbane
    so could it be any letter like A.USR_UID? or does the 'USERS U' bit allow the use of the U?
     
  4. asho444

    asho444 Member

    Joined:
    Feb 7, 2012
    Messages:
    974
    Location:
    Sunny Coast
    It's something my boss drilled into me.
    Always use an alias, always associate a field with an alias so if you or the next clown need to join another table or what ever it's much easier
     
  5. asho444

    asho444 Member

    Joined:
    Feb 7, 2012
    Messages:
    974
    Location:
    Sunny Coast
    Oh. Yes it's an alias.
    The U after the table users allows you to reference it simply as U in the future when adding columns or other tables.
    And can be any charector or set of charecters.

    Say you want to join the user field a second time but join it on the supervisor ID to employee ID you could call the second table S or Super and allow you to differentiate easier

    Linky https://www.w3schools.com/sql/sql_alias.asp
     
    Last edited: May 23, 2019
  6. RnR

    RnR Member

    Joined:
    Oct 9, 2002
    Messages:
    13,012
    Location:
    Brisbane
    Yeah the 'USERS U' allows the alias of 'U' to be used instead of the full table name.

    As asho444 said, its habit. And a good habit :)
     
  7. OP
    OP
    Gunna

    Gunna Member

    Joined:
    Dec 25, 2001
    Messages:
    7,048
    Location:
    Brisbane
    Ok now I get it.

    Thanks for the help
     
  8. Westerntribal

    Westerntribal Member

    Joined:
    Jun 5, 2009
    Messages:
    49
    Location:
    Newcastle
    Im guessing they have used U because its the first letter of the table name. As a convention I usually use the first two letters of the table name. In the past when I was doing a lot of oracle dev it was not uncommon to be writing queries where I would be joining on 20+ tables. It was also not uncommon to have PLSQL blocks with 500-2000 lines of code. This is where it pays off. Also sometimes you might want to join a table on itself in which case it would be U1 and U2.. also if the first two characters of a table happen to be the same then just go to the next one so U then US. SQL server also does some what I consider strange things with alias's. For example I found sometimes sub queries wouldn't work unless you aliased them.. where in Oracle something very similar without the alias would work fine. I havent done a lot of work in MYSQL although Postgres seems to be what all the cool kids are using nowdays anyway
     
    RnR likes this.
  9. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    7,370
    Location:
    Briz Vegas
    This line has designated that the file can be addressed as U, my guess is numpty coder lifted the query from a query that had multiple files where smarter coder designated USERS as U so less typing.

    Code:
    SELECT
      1.id, 2.name
    FROM
      table1 1, table2 2
    WHERE
      1.id = 2.id
     
    Last edited: Jul 12, 2019
  10. neRok

    neRok Member

    Joined:
    Aug 19, 2006
    Messages:
    3,187
    Location:
    Perth NOR
    I've been doing a little SQL lately, and I prefer to write "table AS alias". I find it easier to read, and I presume it has no unintended consequences (my queries work at least). Part of the reason why is because I am writing some code in a statically typed language (Go in this case), so setting a variable to hold a type would look like "var u User", which is written in the opposite order. Using AS just spells it out easier IMO.

    Code:
    SELECT
      t1.id, t2.name
    FROM
      table1 AS t1, table2 AS t2
    WHERE
     t1.id = t2.id
     
    GumbyNoTalent likes this.
  11. asho444

    asho444 Member

    Joined:
    Feb 7, 2012
    Messages:
    974
    Location:
    Sunny Coast
    Certainly good practice if others are going to be working on it, if I know other people are going to be looking at it I will even comment out after and give a brief description of what the table is / does
     

Share This Page

Advertisement: