0

Does the query shown below work? (Sorry for this type of question, but I am a novice and it is not possible for me to run it right now).

SELECT
   table.id 
   SUM(10/ta.value) AS amount 
JOIN tableA ta 
         ON ta.key = t.key

What I am curious about if it is possible to refer to a alias "before" (i.e higher up) it has been assigned.

Thanks!

  • 3
    No, it will not work. You are missing a comma after your first column, and a FROM clause. You can use table aliases (as you have here with ta) in the manner you have. However, in SQL Server you can't refer to a column alias in certain areas (GROUP BY, etc). You should tag your version of SQL Server. – scsimon Sep 19 '19 at 14:32
  • 3
    What DBMS is this about? – mustaccio Sep 19 '19 at 14:40
  • There is a logical sequece in which the clauses are processed. Later clauses can reference earlier clauses. For an example see here docs.microsoft.com/en-us/sql/t-sql/queries/… – Michael Green Sep 20 '19 at 5:59
4

FROM is - logically - evaluated before SELECT, so despite the textual order of your query, ta is known to SUM.

There are however a number of things in your query that won't work:

  1. You need a FROM clause
  2. A JOIN is performed between 2 tables

You can probably rewrite your query to:

SELECT table.id 
     , SUM(10/ta.value) AS amount 
FROM tableA ta
WHERE ...

but perhaps this is what you meant?

SELECT t.id 
     , SUM(10/ta.value) AS amount 
FROM ... as t
JOIN tableA ta 
     ON ta.key = t.key
WHERE ta.key = t.key
GROUP BY t.id

Note that you need a GROUP BY for the latter query

The link provided by Jim Horn below is a good start towards a better understanding, so I'll just add a few lines on a methodology for constructing a query.

Step one, figure out what relations that are involved and how the relate. Example:

FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno

SQL is closed under operations like JOIN, UNION and so forth, meaning that the result is a new relation. It is normally not given a name, but in this case, we can think of it as customer_orders

Step two, what customer_orders are we interested in?

FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019

What details of these customer_orders are we interested in? In this example, I'll assume that we want to know how many orders each customer have:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno

It is not valid to refer to an aggregate function in the where clause, since the where clause is evaluated before the aggregation (count(...)), but there is a similar construction that can be used. Say that we want to know which customers that have more than 20 orders:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20

This is the same as:

SELECT custno, cnt
FROM (
    SELECT c.custno, count(o.orderno) as num_orders
    FROM customers as c
    JOIN orders as o 
        ON c.custno = o.custno
    WHERE year(order_date) = 2019
    GROUP BY c.custno
) as customer_orders
WHERE num_orders > 20

Finally, you may want to retrieve the rows in a particular order.

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20
ORDER BY count(o.orderno) desc -- most orders first

Up until the last step the result is a relation, but the order by invalidates the fundamental property that a relation is unordered. It may seem like an irrelevant note, but it is much easier for the DBMS in question to optimize a query if it does not have to take the order of rows in consideration.

| improve this answer | |
1

Lennart's answer is correct. To make things a little clearer for you I'll ask that you change your definition of 'before' from this..

SWEATY (SELECT)
FEET (FROM, with JOINs)
WILL (WHERE)
GIVE (GROUP BY)
HORRIBLE (HAVING)
ODORS (ORDER BY)  (Good luck getting that out of your head)

... to follow the Query Order of Execution (link) which is how the query is processed, and is a different order then how it is typed.

Looking at the link, any alias defined in a section can be used by any section below it.

| improve this answer | |
  • 1
    The link has generally correct info but misinforms in some minor details: the ON clause(s) are part of the FROM clause and there is no OUTER clause. LEFT/RIGHT/FULL OUTER JOIN are operators. – ypercubeᵀᴹ Sep 19 '19 at 17:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.