0

First, let me note that I am aware of the other threads with a similar question, but they didn't help my understanding very much. On the opposite, I now sometimes run into the problem that assigning aliases ruins my code, as described below.

So I got said error message very often, and in turn started to give aliases to those subqueries which I thought were 'derived tables', but sometimes when doing so, I now get the message 'You have an error in your SQL syntax' instead, and after removing the 'AS ...' statement, everything runs fine. So I am really trying to figure out when exactly something is a derived table and hence needs and alias, and when not. I will give you an example: Given some tables P, LTP, and T, the following query runs flawless:

SELECT DISTINCT pname FROM P WHERE P.pnr IN (SELECT pnr FROM LTP WHERE lnr='L1' AND tnr IN (SELECT tnr FROM T WHERE gewicht>10));

How are the statements in the brackets not derived tables though? I would have assumed that in this case I would have had to give them aliases like this:

SELECT DISTINCT pname FROM P WHERE P.pnr IN (SELECT pnr FROM LTP WHERE lnr='L1' AND tnr IN (SELECT tnr FROM T WHERE gewicht>10) AS TNEW) AS LTPNEW;

but both of these ruin the code. I would really appreciate if somebody could point out to me what exactly I am misunderstanding.

azureai
  • 181
  • 1
  • 13

1 Answers1

3

If the subquery is in the table_references portion of a query (the FROM clause and all accompanying JOINs), it needs to include an alias.

If the subquery appears elsewhere, like in the WHERE or SELECT section, it's just a regular subquery and no aliasing is required.

From the documentation:

Derived tables is the internal name for subqueries in the FROM clause.

As a rule of thumb, if you can reference a column from the subquery by name, then the subquery needs an alias to prevent ambiguity.

Mr. Llama
  • 20,202
  • 2
  • 62
  • 115
  • also if the results is in a join section of a query, if i am correct? – Jester Apr 01 '16 at 14:57
  • Correct. The `JOIN` is a subsection of the `FROM` clause. – Mr. Llama Apr 01 '16 at 14:58
  • Never heard of describing the `JOIN` as a subsection to the `FROM` clause. Might as well just add it for clarity? – Jester Apr 01 '16 at 15:02
  • @Jester - Done! The `FROM` keyword marks the start of the [`table_references`](https://dev.mysql.com/doc/refman/5.7/en/join.html) region, which `JOIN` is a part of. – Mr. Llama Apr 01 '16 at 15:13
  • Thank you, I checked this for a few of my examples and this seems to be indeed true. But for example in the following, it works with and without the bold part. Why is that? SELECT pname FROM P WHERE pnr IN (SELECT pnr FROM (SELECT pnr, COUNT(DISTINCT lnr) _**AS ALIAS**_ FROM (SELECT * FROM LTP WHERE lnr IN (SELECT DISTINCT lnr FROM L WHERE status<=30)) AS CHOICE GROUP BY pnr) AS LTPCHOICE);? I think I also don't understand the last part of your answer, what do you mean by 'if you can reference a column from the subquery by name', exactly? – azureai Apr 01 '16 at 15:21
  • 1
    @see that means you can alias a column name as in your query.For example: if you alias COUNT(DISTINCT Inr) As some then output table will have a column like"some" but if won't alias COUNT(DISTINCT Inr) then the ouput table will have a column like "COUNT(DISTINCT Inr)".... – Shiv Apr 01 '16 at 15:54