SQL understanding help?

[TheJoey]

i have a querry which is:

 

select a.name, a.weight

from part a

where a.colour= 'blue'

order by weight descending;

 

Now what i dont understand is why is there a a infront of name weight and colour but then when you order by weight you dont put a "a"

[gizmola]

You don't need the aliases.  All the "part a" does is create an alias for part, that you can use rather than having to specify the full table name.

 

Because you only have one table, there is no possible ambiguity, and thus no need for prefixing columns with the table name.

 

That is used when you have multiple tables, in the cases where you are joining them together.

 

 

[TheJoey]

Well say i had two tables parts & Customer names

Would i use the alias's then so that i can join the 2 tables?

 

[gizmola]

Well say i had two tables parts & Customer names

Would i use the alias's then so that i can join the 2 tables?

 

All an alias is -- as the name suggests is an alternative to using the full table name.

 

MySQL supports a few different join syntaxes, so in this case I'll use a simple inner join specified in the WHERE clause to illustrate.

 

SELECT foo.id, foo.name, bar.id, bar.name
FROM foo, bar
WHERE bar.id = foo.bar_id

 

This is exactly the same, only using aliases:

 

SELECT f.id, f.name, b.id, b.name
FROM foo f, bar b
WHERE b.id = f.bar_id

 

Technically, you only need to prefix the table name where there is ambiguity.  In the example, I assume that table foo and bar both have columns named id and name.  This is ambiguous, so if I specified "SELECT id", I'd get an error because mysql doesn't know which id column (foo.id or bar.id) I want.  However, we'll assume that bar_id only exists in table foo, and is a foreign key from the bar table.  That would not be ambigous, so I don't technically need to specify the table prefix of foo, as mysql knows all the columns that exist in the 2 tables.

 

I could thus, write the query this way and it will still work.

 

SELECT f.id, f.name, b.id, b.name
FROM foo f, bar b
WHERE b.id = bar_id