I'm reading this article and I'm trying to understand this SQL statement but I am still somewhat new to SQL.
I'm not sure what comment and c refer to.
I think one of them is the table name but I am not sure of the other. Also, apparently there is a subquery within it which I have not had any experience with:
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
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 are all the fields to be selected, with the prefix of c
which is the alias later to the comment
table.
The query in a query is a subquery, which runs that query which does a like and concatenates .clineage
with %
(which is the wildcard). This subquery result is saved in replies
.
The results are ordered by linea
.
Comment is a table and c is an alias for the last comment table reference. So, c.id refers to the id column in the last instance of the comment table.
c
is an alias for a table named comment
defined with comment as c
.
"comment" is the table name, and "c" is just an alias for it to save typing. The query gets a list of comments from the comments table. It returns a 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 replies to this comment, as specified by (SELECT COUNT(*) FROM comment where comment.lineage LIKE (CONCAT(c.lineage,'%')) AND comment.lineage!=c.lineage) as replies
The as
keyword creates an alias for something so that you can refer to it later unambiguously. So, comment
refers to the table and c
is an alias for that same table. This is especially useful since you're referring to comment
in two different contexts (in both the main query and 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
comment
is indeed the name of a table in this query. c
is an alias used for that table (in the syntax comment as c
) so that elsewhere in the query the comment
table can be referenced with simply a c
instead of the entire table name.
In this particular case, where the sub-query is also querying from the same table, the alias allows it to reference that same table from the parent query. This is useful here because, in the context of the sub-query, c.lineage
is a static value (per row returned from the parent query) which is used to filter rows in the sub-query (with comment.lineage
). The sub-query can then return a single value per row of the parent query, and that value is aliased to the name replies
in the result.
You were very close in what you thought. comment is the table name, and so is c. See the line that says FROM comment as c' that is labeling the comment as c. The subquery is everything inside those outer ()