CODEWITHSUNDEEP
sql
ritesh gyanchandani
ritesh gyanchandani

Reputation: 67

Having query in SQL aliases

The following SQL statement selects all the orders from the customer with CustomerID=4 ("Around the Horn"). We use the Customers and Orders tables, and give them the table aliases of c and o, respectively. (Here we have used aliases to make the SQL shorter):

SELECT o.OrderID, o.OrderDate, c.CustomerName
  FROM Customers AS c, Orders AS o
 WHERE c.CustomerName="Around the Horn"
   AND c.CustomerID=o.CustomerID;

Can you please explain this syntax considering I am complete beginner.

Upvotes: 0

Views: 1082

Answers (3)

Saheem Khizar
Saheem Khizar

Reputation: 21

Here is a simpler version of the above code without using Alias (b/c that is what confused me in the start)... Without alias, it is much simpler to read this code as below:

SELECT OrderID, OrderDate, CustomerName
FROM Customers, Orders
WHERE  Customers.CustomerID=Orders.CustomerID

Upvotes: 2

Vitto
Vitto

Reputation: 399

With the SELECT statement you tell which columns of the table(s) you need. If there are more tables with the same column names you have to specificy also the table name (tableName.columnName).

With the FROM statement you tell the tables from which you want to select the datas. In this query there are two tables separated from a comma, this is not a good idea, because there is the JOIN statement to merge two tables in the correct way.

Finally with the WHERE statement you can tell conditions to filter the data. In this query you want only the customers with name = 'Around the Horn' and you also tell to select only the rows with the same customer id in the customer and order tables. This last condition should be specified with a join condition in the FROM:

FROM Customer AS c JOIN Orders AS o ON c.CustomerID = o.CustomerID

Otherwise using the comma in the FROM it does the cartesian product between the two tables and then it select only the correct rows with the second WHERE condition.

I suggest you to read a sql guide for beginners

Upvotes: 0

OscarAkaElvis
OscarAkaElvis

Reputation: 5714

What is the variable param of your SQL? if it is a text "Around the Horn", the SQL is correct. Anyway always is better to use ids to filter. It would be better if you have the id 4 before launching the query to do in this way:

SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerId=4 AND c.CustomerID=o.CustomerID;

And what exactly do you want to explain? you already explained it well :)

Aliases if to simplify or to use in a SQL in which there could be tables with same column name (like yours), so you put the prefix of "tableAlias." before the column to set from what table are you using the column. In other way you could generate an error of "column ambiguously defined".

Cheers.

Upvotes: 0

Related Questions