Very newb question: Why do I have to specify FROM in a "Join" Query? 1

[kamazon]

I know you have to specify from so the server knows where you are trying to pull this information from, and it's the first thing the server looks at, but look at the following (or any join statement) example:

http://www.tizag.com/sqlTutorial/sqljoin.php

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
LEFT JOIN invoices
ON employees.id = invoices.EmployeeID


The user already specifies the tables in the SELECT statement in employees.Lastname, employees.Firstname, invoices,Sales, and invoices.Price tables. Why do you have to put in FROM employees? The reason I ask is because this is slightly confusing. Why not pick:
FROM invoices? Why specify FROM employees instead?

-Can you do a JOIN statement from 3 or more tables? If so, what table do you pick in the FROM line?

Thanks in advance for my newb questions! I am trying to learn SQL as well as I can, so I might bug you guys with more if that's ok.

 

[r937]

what would you say about the table prefix X in this query --

select X.foo
  from (
       select t1.qux * 3.1416  as foo
         from t1
       inner
         join t2
           on t2.B = t1.A
       ) as X

to answer your question, you must specify the tables in the FROM, you cannot just leave one out because, as in this case, a table prefix in the SELECT might not always correspond to an actual table

as for joining 3 or more tables, that's very commonplace




r937.com | rudy.ca

 

[SQLBill]

To back up what Rudy said, your query could also be written as:

SELECT e.Lastname, e.Firstname, i.Sale, i.Price
FROM employees e
LEFT JOIN invoices i
ON e.id = i.EmployeeID

Yes, you can join three or even more tables. The first table probably should be the one where the majority of the data will come from, then the rest will be in the order of how they link to each other.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

To add to the thread....I also once had it explained to me that SQL Server 'reads' queries in the following order.

1. FROM - checks all the tables exist
2. JOIN if any - joins the tables and gets the joined data
3. WHERE - trims the returned data based on this criteria
4. GROUP BY - groups the results
5. HAVING - further trims the results
6. SELECT - now it picks what you want returned out of the larger amount of data
7. ORDER BY - orders the data to be returned.

So, as you can see, since the SELECT doesn't get 'acted on' until the end....having the table name there doesn't help. It still needs all the tables in the FROM as that is what is acted upon first.

I wish I still had the explaination, but I lost it at my last job.

-SQLBill

Posting advice: FAQ481-4875

 

[harebrain]

All nice rationalizations, but the simple fact of the matter is that syntax is syntax. FROM is required; just because you can sometimes infer the FROM clause from optional parts of the SELECT syntax, it doesn't mean that you don't need the required parts of syntax.

Your original query could just as easily be written as

SELECT Lastname, Firstname, Sale, Price
FROM employees
LEFT JOIN invoices
ON employees.id = invoices.EmployeeID

Where would you be without the FROM now? Where would you hang the JOIN? How could you specify the JOIN type?

 

[r937]

SQLBill, here's a posting Celko made on the comp.databases.ms-sqlserver forum about the order of evaluation of query clauses:

http://www.thescripts.com/forum/post279650-6.html

r937.com | rudy.ca

 

[rac2]

Isnt it somewhat of an historical accident? That is how you do it in SQL because that is how the rules evolved over the years.

Think about how you would design a structured query language to retrieve data that is stored in a relational database. One of the issues you would need to address is how to specify which table the data are in. I never thought about it but I suppose there is no need at all for a FROM clause. Maybe you could design a workable, parsable syntax in which the table name was always connected to the column name. This might leave you scratching you head over a means of specifying aliases for table name which are darned convenient sometimes. r937 gives a good example of this.

The FROM clause is a handy place to specify those aliases.

Also note that before there was the JOIN syntax your query might have been written like this.

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees, invoices
WHERE employees.id = invoices.EmployeeID[COLOR=red](+)[/color]

That is Oracle syntax, the (+) means LEFT JOIN, and the conditions for the join are stated in the WHERE clause. Other RDBMS have different notations, I think I saw +=, maybe in MySQL, or *=. A RIGHT JOIN was =+ and =*. Or maybe the other way around. In any case no one would use that notation if they were coding SQL in a system that provided the JOIN syntax.

And you could write your query differently using the JOIN syntax and mentioning the invoices table after the FROM keyword.

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM invoices
RIGHT JOIN employees
ON employees.id = invoices.EmployeeID

The significance of the LEFT and RIGHT JOINing, as you have probably picked up on already in your study is in regard to

deficient tables

. Deficient doesnt mean they are defective, it means they may not always have rows which meet the join conditions. I am a coder, I dont have any invoices, none of my company's invoices have my name on them. As you know if you do the simple INNER JOIN, my name will be left out of the report. Actually that is OK by me, but lots of times you wish to see a row from the primary table even when there are no rows in the joined table. So you write an OUTER JOIN, a LEFT OUTER JOIN or a RIGHT OUTER JOIN depending on whether the table being joined is deficient (left join), or the table it is joined to is deficient(right join).


As to whether there are performance issues that are affected by mentioning a particular table as the one to SELECT ... FROM MyTable m JOIN OtherTable o ON m.id = o.id I dont know. There may be. Part of the database system is a query optimizer which looks at factors such as indexes, table size, conditions, and translates our eligant SQL statements into brutally ugly machine code which actuallys reads the data files and yields a recordset. This is the area SQLBill addressed nicely. At this stage of your learning you probably dont need to think about that. I certainly dont know anything about it and I have been writing SQL for seven years.

 

[kamazon]

Wow did I totally join the right forum or what (that was a rhetorical question)! Thanks for all the answers! Explanations, links, and friendly advice all rolled into one. Thanks again ~

 

[SQLBill]

Rudy,

Thanks for the link! Now, I can put that link in my notes. It was deserving of a star.

-SQLBill

Posting advice: FAQ481-4875