Can someone explain this SQL query to me?

Question

I am reading this article and I am trying to understand this SQL statement, but I am still somewhat new to SQL.

I am not sure which comments and c are referencing. I think one of them is the name of the table, but I'm not sure about the other. In addition, apparently, there is a subquery inside it with which I had no experience:

SELECT c.id, c.user_id, c.body, c.deep, c.lineage, c.parent_id, (SELECT COUNT(*) FROM comment WHERE comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage != c.lineage) AS replies FROM comment as c ORDER BY c.lineage 

Answer 1

 SELECT c.id, c.user_id, c.body, c.deep, c.lineage, c.parent_id, ( SELECT COUNT(*) FROM comment where comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage!=c.lineage) as replies FROM comment as c order by c.linea 

The first list is all the fields that you need to select, with the prefix c , which is an alias later in the comment table.

A query in a query is a subquery that executes this query, which does similar, and combines .clineage with % (which is a wildcard). This subquery result is stored in replies .

Results are ordered on linea .

Answer 2

c is an alias for a table named comment defined with comment as c .

Answer 3

comment indeed the name of the table in this query. c is the alias used for this table (in the syntax for comment as c ), so you can only refer to c in the other place in the query in the comment table with c instead of the entire table name.

In this particular case, when an auxiliary query also queries from the same table, the alias allows it to refer to the same table from the parent query. This is useful here because in the context of the subquery, c.lineage is a static value (for each row returned from the parent query) that is used to filter the rows in the subquery (with comment.lineage ). Then the subquery can return one value for each row of the parent query, and this value is smoothed with the name replies as a result.

Answer 4

“comment” is the name of the table, and “c” is just an alias to save the set. The request retrieves the list of comments from the comment table. It returns the number of columns specified by c.id, c.user_id, c.body, c.deep, c.lineage, c.parent_id , as well as the number of responses to this comment, as indicated by (SELECT COUNT(*) FROM comment where comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage!=c.lineage) as replies

Answer 5

A comment is a table, and c is an alias for the last link of the comment table. So c.id refers to the id column in the last instance of the comment table.

Answer 6

The as keyword creates an alias for something so that you can refer to it later unambiguously. Thus, comment refers to a table, and c is an alias for the same table. This is especially useful since you reference comment in two different contexts (both in the main request and in the subquery).

It also allows you to assign the name replies to the result of your subquery:

 (SELECT COUNT(*) FROM comment WHERE comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage!=c.lineage) as replies 

Answer 7

You were very close to what you thought. comment is the name of the table, as well as c. See Line for FROM comment as c ', which is marked as comment as c. A subquery is everything inside these external ()