3

I have a simple query in my code (shown below) written by my colleague. What does t mean here? Also what is the role of the ; inside the query? I am dead sure that t is not any table, nor any field anywhere in my database. And guess what this query works!!

string query = @"SELECT COUNT(*) FROM (SELECT AttemptNo FROM attempt_exercise 
                 WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode)t;
                ";

The code follows like this (for any other info if required):

MySqlCommand cmd = new MySqlCommand(query, _conn);
cmd.Parameters.AddWithValue("@uId", uId);
cmd.Parameters.AddWithValue("@eId", eId);
cmd.Parameters.AddWithValue("@mode", mode);
attemptCount = Convert.ToInt32(cmd.ExecuteScalar());
_conn.Close();
return attemptCount;
nawfal
  • 70,104
  • 56
  • 326
  • 368
  • @BenLee No, when I remove the t, I get the error `Every derived table must have its own alias` – nawfal Feb 25 '12 at 07:20

6 Answers6

3

Your colleague created a query (SELECT COUNT(*)) with a subquery that he named t. This t is just a temporary table name which refers to 

SELECT AttemptNo FROM attempt_exercise 
  WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode

He could have feasibly named it temp to be a bit more explicit. The reason that this becomes like a table is because, in MySQL, a SELECT query returns rows of data which act like a table. So, this inner query gets the AttemptNo, and creates a temporary table t. The outer query then counts this data.

The ; inside the query is to make it a full statement when the string query is called by the program. If this weren't included, the String query wouldn't contain a valid MySQL statement. The final ; is to complete the assignment for the variable.

simchona
  • 1,878
  • 3
  • 19
  • 18
  • How is the query `SELECT AttemptNo FROM attempt_exercise WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode` a table first of all? It returns a value, not a table name isn't it? – nawfal Feb 25 '12 at 07:21
  • @nawfal it returns rows of tables which make up a "table" of data. Then, `t` names this as an alias – simchona Feb 25 '12 at 07:22
  • Oh, I wished if you added that info to your answer so that it becomes clear and perfect :) – nawfal Feb 25 '12 at 07:23
  • The `;` inside the query is not always needed. It is surely needed if you have 2 or more queries/statements. It's the separator (delimiter), so the SQL parser knows where one statement finishes and where the next starts. – ypercubeᵀᴹ Feb 25 '12 at 07:52
  • @ypercube I disagree, the final `;` is part of the String definition for `query`, isn't it? It's not part of the SQL statement itself. – simchona Feb 25 '12 at 07:53
  • @simchona: Right, I was referring to the inside `;`, edited my comment. – ypercubeᵀᴹ Feb 25 '12 at 07:57
  • @ypercube Depending on how the queries were run, would you think it'd be safer to have the inner `;`? I'm not claiming to be an expert, but MySQL always yelled at me if I didn't end statements with a semicolon – simchona Feb 25 '12 at 08:01
  • It depends on how you run your queries. Different interfaces have different behaviours. Some require a semicolon. Some do not and even disbehave if you do add one (those usually cannot run more than one statement per call) – ypercubeᵀᴹ Feb 25 '12 at 08:05
  • But your answer is fine. Those interfaces that do not require a semicolon, probably understand when you have just one statement and it (the ;) themselves. – ypercubeᵀᴹ Feb 25 '12 at 08:07
  • @ypercube Yes, that semicolon is unnecessary :) – nawfal Feb 25 '12 at 09:06
  • @nawfal: Since you tested that, we know. What is the language and inteface you are using? C#, C++, PHP, other? – ypercubeᵀᴹ Feb 25 '12 at 09:08
  • @ypercube C# [1 more to go] :) – nawfal Feb 25 '12 at 09:13
  • 1
    @simchona - It's more like an "inline view" than it is a "table". – MatBailie Feb 25 '12 at 09:43
3

t is an alias for your subquery, and you need it.

So you could have written:

SELECT COUNT(*) FROM attempt_exercise 
WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode;

and that would have been equivalent.

But were you to try to join your subquery to something else, you would have likely seen the need a bit sooner:

SELECT COUNT(*) FROM 
   (SELECT AttemptNo FROM attempt_exercise 
    WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode) t
JOIN AttemptStatisticsTableOfSomeSort a
    ON t.AttemptNo = a.AttemptNo;
Dave Markle
  • 95,573
  • 20
  • 147
  • 170
1

It is just an alias for the nested query. The semi colon inside the query is a statement terminator - its part of the ANSI sql standard.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
1

An alias to the subquery. If subquery appears as tablesource in FROM or JOIN clauses - it should have an alias.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
1

All temporary tables created in this manner (ie. in the FROM clause) MUST have an alias they can be referred to by. In this case, the alias is t. If the coder were more clear in their writing, they wouldn't have omitted the optional AS before it.

The point is, it's just a temporary name and not really important in this scenario, but it required to make it work.

That said, the following is probably more efficient:

SELECT COUNT(DISTINCT AttemptNo) FROM attempt_exercise WHERE FK_UId = @uId AND FK_EId = @eId AND Mode = @mode
Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • Nice answer. Thanks. I appreciate your effort to give a more efficient query, but no in my case I need not just distinct, but all the `AttemptNo` :) Nevertheless tweak-able, thanks :) – nawfal Feb 25 '12 at 07:26
  • @kolink: The OP's query does not count distinct `Attemptno`. It counts all of them. – ypercubeᵀᴹ Feb 25 '12 at 07:55
0

The semi colon is a line delimiter for MySql commands. Have a look under the first bullet point on here for more info: http://dev.mysql.com/doc/refman/5.0/en/entering-queries.html

Mere Development
  • 2,439
  • 5
  • 32
  • 63