1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL why do i need to use FROM if I SELECT table.column?

Discussion in 'General Scripting Chat' started by Mutikasa, Sep 25, 2015.

  1. Mutikasa

    Mutikasa Power Member

    Joined:
    May 23, 2011
    Messages:
    581
    Likes Received:
    207
    Yes, if i do SELECT table.column then why do I need to do FROM when I already specified table?
    can I just write "select table.column" and that's it?
     
    Last edited: Sep 25, 2015
  2. bytzu

    bytzu Junior Member

    Joined:
    Jun 30, 2011
    Messages:
    102
    Likes Received:
    142
    In case of a simple select from a single column the proper way to do is like this:

    SELECT column_name,column_name FROM table_name;
    or
    SELECT * FROM table_name;

    you need to specify column name in case SQL Joins or when you select from 2 or more tables having same columns name. For ex

    SELECT Orders.ID AS custom_alias1, Customers.ID AS custom_alias2, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

    As you can see ID is a column with same name in both tables and you need to specify the table name for each ID when creating the Query. Also it's good to give a name (aliases) to column fields having the same name in order not to mix the values.
     
  3. Mutikasa

    Mutikasa Power Member

    Joined:
    May 23, 2011
    Messages:
    581
    Likes Received:
    207
    You see, there it goes again. Why do have to type "FROM Orders" even when we choose columns from different tables? FROM in this case makes no logic to me
     
  4. Diplomat

    Diplomat Jr. VIP Jr. VIP

    Joined:
    Oct 25, 2011
    Messages:
    948
    Likes Received:
    440
    Home Page:
    Well.. in that join query you are using "FROM Orders" as place where that query takes it's data. If you wouldn't use it.. then MySQL would tell you for "ON Orders.CustomerID=Customers.CustomerID" for this that it has no idea about the Orders.CustomerID column. From that "FROM" you are getting "Orders" table. From "INNER JOIN" you are adding "Customers" table.

    I'm not 100% sure, but it should be something like this:

    1. Get all records from "Orders.CustomerID"
    2. Add "Customers" table
    3. Find rows from "Customers" table where CustomerID matches CustomerID column from "Orders"
     
  5. Mutikasa

    Mutikasa Power Member

    Joined:
    May 23, 2011
    Messages:
    581
    Likes Received:
    207
    but you pulled columns from Orders AND from Costumers and yet only put "FROM Orders" in query.
    FROM means from some table, but if we already specify table in query like "SELECT from Orders.ID" then why we need to put FROM for?
     
  6. Diplomat

    Diplomat Jr. VIP Jr. VIP

    Joined:
    Oct 25, 2011
    Messages:
    948
    Likes Received:
    440
    Home Page:
    SELECT Orders.ID tells MySQL server which column to pick.. not from. Orders.ID is like that because you are using join in your query.. it helps to identify which column it needs to take.

    SELECT * FROM clients WHERE id = 5; <-- no need for table identification because we are using only one table
    SELECT client.id, client.name, order.id, order.clientid FROM client JOIN order ON client.id = order.clientid WHERE client.id = 5; <-- table needs identification because you have 2 tables now that you are using (no idea if that query is correct because I didn't test it).
     
  7. fidodido

    fidodido Junior Member

    Joined:
    Aug 12, 2015
    Messages:
    113
    Likes Received:
    27
    The answer to this without getting in too deep is ... because ANSI SQL say so.
     
  8. Mutikasa

    Mutikasa Power Member

    Joined:
    May 23, 2011
    Messages:
    581
    Likes Received:
    207
    let's leave the JOIN out to make ti simpler.
    If I have table "locoloco" and column "id" and wanna do SELECT like:
    "SELECT id FROM locoloco" <- this works
    and if I wanna do
    "SELECT locoloco.id" <- this gives "#1109 - Unknown table 'locoloco' in field list "

    I have to do:
    "SELECT locoloco.id FROM locoloco" <-this works just like the first one.
    But, where is the logic here? Why do they force me to use FROM locoloco when is perfectly clear which table I am getting column from?
     
  9. Diplomat

    Diplomat Jr. VIP Jr. VIP

    Joined:
    Oct 25, 2011
    Messages:
    948
    Likes Received:
    440
    Home Page:
    That's just how SQL works.. just because it's clear for you doesn't mean that it's clear for MySQL server.