SQL question: FROM and AS

  • Thread starter Thread starter Peter Jay Salzman
  • Start date Start date
P

Peter Jay Salzman

I'm learning SQL, and have two questions. The book I'm using has the
following SQL command:


SELECT tblClients.Organization, Trim([Firstname] & " " & [Lastname])
AS Contact, tblClients.WorkPhone, tblClients.State,
tblClients.LastContact FROM tblClients
WHERE (((tblClients.State)="IL")) ORDER BY tblClients.Organization;


There are a few things that I'm having trouble with.

1. If we identify records to select by both the field _and_ the table,
like "tblClients.Organization" and "tblClients.State", then why do
use the "FROM" clause to identify the table? It seems redundant
since we're already telling SQL which table to select the records
from.

2. I don't understand the book's explanation of what the AS keyword
does.

3. The clause

WHERE (((tblClients.State)="IL"))

looks like it has a redundant set of parentheses. What purpose does
the outer parentheses serve? To me, it looks like:

WHERE ((tblClients.State)="IL")

would be enough to group the expression for the WHERE clause.


Thanks!
Pete
 
The FROM clause tells the SQL Server where to get the records from.
Select * FROM tbldata will select all records from tbldata. It also defines
all the join types.

My understanding of the AS keyword is that you are setting an alias for what
could be a complicated / ugly field name
eg SELECT Inventory_ID From Inventory
This would produce a result set with a field called Inventory_ID - this
would go on your reports etc and may not look very good
If on the other hand you did SELECT Inventory_ID AS Stockcode From
Inventory - this would produce a result set with a field call Stockcode
which looks better and saves time having to change reports etc.

Access is know to add superfluous parentheses - in the example given ie.
WHERE ((tblClients.State)="IL")

you could get away without having any parentheses

Hope this helps!
 
Hola! Thanks for the reply! One question about your reply. In the
SELECT example I gave below, the basic form was:

SELECT table.field FROM table;

I guess I'm a little confused by this. If we select things of the from:

table.field

why do we also need to specify:

FROM table

since "table.field" tells it all? :-)


Your explanation of AS was great. Thanks!

Pete



Newbie said:
The FROM clause tells the SQL Server where to get the records from.
Select * FROM tbldata will select all records from tbldata. It also defines
all the join types.

My understanding of the AS keyword is that you are setting an alias for what
could be a complicated / ugly field name
eg SELECT Inventory_ID From Inventory
This would produce a result set with a field called Inventory_ID - this
would go on your reports etc and may not look very good
If on the other hand you did SELECT Inventory_ID AS Stockcode From
Inventory - this would produce a result set with a field call Stockcode
which looks better and saves time having to change reports etc.

Access is know to add superfluous parentheses - in the example given ie.
WHERE ((tblClients.State)="IL")

you could get away without having any parentheses

Hope this helps!

Peter Jay Salzman said:
I'm learning SQL, and have two questions. The book I'm using has the
following SQL command:


SELECT tblClients.Organization, Trim([Firstname] & " " & [Lastname])
AS Contact, tblClients.WorkPhone, tblClients.State,
tblClients.LastContact FROM tblClients
WHERE (((tblClients.State)="IL")) ORDER BY tblClients.Organization;


There are a few things that I'm having trouble with.

1. If we identify records to select by both the field _and_ the table,
like "tblClients.Organization" and "tblClients.State", then why do
use the "FROM" clause to identify the table? It seems redundant
since we're already telling SQL which table to select the records
from.

2. I don't understand the book's explanation of what the AS keyword
does.

3. The clause

WHERE (((tblClients.State)="IL"))

looks like it has a redundant set of parentheses. What purpose does
the outer parentheses serve? To me, it looks like:

WHERE ((tblClients.State)="IL")

would be enough to group the expression for the WHERE clause.


Thanks!
Pete
Click to expand...
Click to expand...
 
Ahhh... never mind. I started to read about how to create JOINs using
SQL sytax. :)

Thanks,
Pete

Peter Jay Salzman said:
Hola! Thanks for the reply! One question about your reply. In the
SELECT example I gave below, the basic form was:

SELECT table.field FROM table;

I guess I'm a little confused by this. If we select things of the from:

table.field

why do we also need to specify:

FROM table

since "table.field" tells it all? :-)


Your explanation of AS was great. Thanks!

Pete



Newbie said:
The FROM clause tells the SQL Server where to get the records from.
Select * FROM tbldata will select all records from tbldata. It also defines
all the join types.

My understanding of the AS keyword is that you are setting an alias for what
could be a complicated / ugly field name
eg SELECT Inventory_ID From Inventory
This would produce a result set with a field called Inventory_ID - this
would go on your reports etc and may not look very good
If on the other hand you did SELECT Inventory_ID AS Stockcode From
Inventory - this would produce a result set with a field call Stockcode
which looks better and saves time having to change reports etc.

Access is know to add superfluous parentheses - in the example given ie.
WHERE ((tblClients.State)="IL")

you could get away without having any parentheses

Hope this helps!

Peter Jay Salzman said:
I'm learning SQL, and have two questions. The book I'm using has the
following SQL command:


SELECT tblClients.Organization, Trim([Firstname] & " " & [Lastname])
AS Contact, tblClients.WorkPhone, tblClients.State,
tblClients.LastContact FROM tblClients
WHERE (((tblClients.State)="IL")) ORDER BY tblClients.Organization;


There are a few things that I'm having trouble with.

1. If we identify records to select by both the field _and_ the table,
like "tblClients.Organization" and "tblClients.State", then why do
use the "FROM" clause to identify the table? It seems redundant
since we're already telling SQL which table to select the records
from.

2. I don't understand the book's explanation of what the AS keyword
does.

3. The clause

WHERE (((tblClients.State)="IL"))

looks like it has a redundant set of parentheses. What purpose does
the outer parentheses serve? To me, it looks like:

WHERE ((tblClients.State)="IL")

would be enough to group the expression for the WHERE clause.


Thanks!
Pete
Click to expand...
Click to expand...
Click to expand...
 
My understanding, and again I'm quite new to this as well, is . . . .

Try and think of a simple select query to be made up of three parts:

SELECT i.e. tells SQL Server what fields to return
FROM i.e. tells SQL Server where to find the fields
WHERE i.e. tells SQL Server what filter to put on the recordset

In the following statement:
SELECT mytable.empid, mytable.empname
FROM mytable
WHERE empid = "1"

the information that isn't required is the tablename in the SELECT line - it
is here that the code is superfluous not in the FROM clause as the FROM
clause is ALWAYS required. There is no point using mytable.empid because
the SQL Server will only be looking in the mytable anyway as this is the
only table listed in the FROM clause.

It may speed things up if you have more complicated JOINS in the FROM clause
to use 2part naming (ie tablename.fieldname) or it may be necessary to use 2
part naming if you have two or more tables in the FROM clause that have the
same field name as you need to tell SQL Server which one to choose.

Hope this helps


Peter Jay Salzman said:
Hola! Thanks for the reply! One question about your reply. In the
SELECT example I gave below, the basic form was:

SELECT table.field FROM table;

I guess I'm a little confused by this. If we select things of the from:

table.field

why do we also need to specify:

FROM table

since "table.field" tells it all? :-)


Your explanation of AS was great. Thanks!

Pete



Newbie said:
The FROM clause tells the SQL Server where to get the records from.
Select * FROM tbldata will select all records from tbldata. It also defines
all the join types.

My understanding of the AS keyword is that you are setting an alias for what
could be a complicated / ugly field name
eg SELECT Inventory_ID From Inventory
This would produce a result set with a field called Inventory_ID - this
would go on your reports etc and may not look very good
If on the other hand you did SELECT Inventory_ID AS Stockcode From
Inventory - this would produce a result set with a field call Stockcode
which looks better and saves time having to change reports etc.

Access is know to add superfluous parentheses - in the example given ie.
WHERE ((tblClients.State)="IL")

you could get away without having any parentheses

Hope this helps!

Peter Jay Salzman said:
I'm learning SQL, and have two questions. The book I'm using has the
following SQL command:


SELECT tblClients.Organization, Trim([Firstname] & " " & [Lastname])
AS Contact, tblClients.WorkPhone, tblClients.State,
tblClients.LastContact FROM tblClients
WHERE (((tblClients.State)="IL")) ORDER BY tblClients.Organization;


There are a few things that I'm having trouble with.

1. If we identify records to select by both the field _and_ the table,
like "tblClients.Organization" and "tblClients.State", then why do
use the "FROM" clause to identify the table? It seems redundant
since we're already telling SQL which table to select the records
from.

2. I don't understand the book's explanation of what the AS keyword
does.

3. The clause

WHERE (((tblClients.State)="IL"))

looks like it has a redundant set of parentheses. What purpose does
the outer parentheses serve? To me, it looks like:

WHERE ((tblClients.State)="IL")

would be enough to group the expression for the WHERE clause.


Thanks!
Pete
Click to expand...
Click to expand...
Click to expand...
 
Back
Top