SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, August 31, 2006

Why does this query work but that one does not

SQL Apprentice Question
Why does this query work;

SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location
FROM employee_enh e1 JOIN employee_enh e2
ON e1.domicile = e2.domicile
JOIN department d1 JOIN department d2
ON d1.location = d2.location
ON e1.dept_no = d1.dept_no
WHERE e1.emp_no <> e2.emp_no


but this one does not?;


SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location
FROM employee_enh e1 JOIN employee_enh e2
ON e1.domicile = e2.domicile
JOIN department d1 JOIN department d2
ON e1.dept_no = d1.dept_no
ON d1.location = d2.location
WHERE e1.emp_no <> e2.emp_no


All statements contained in both queries are identical. The change
between the two is that the order of the last two ON statements are
switched.


Query Analyzer gives me the following error;
The column prefix 'e1' does not match with a table name or alias name
used in the query.


What is the rule?


Celko Answers
>> What is the rule? <<


Infixed joins are executed from left to right. The ON clause (if any)
is associated with the most recent JOIN clause. Parentheses are
executed in their order of nesting. No great surprises here -- pretty
much like other scoping rules in 3GLs

A derived table can be constructured with parens and an AS clause.
Only the alias is available to containing queries, not the contained
table names. This catches people.

No comments: