Can someone explain this SQL query to me?

Question

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

You might also be interested in this question [When to use SQL Table Alias](http://stackoverflow.com/questions/198196/when-to-use-sql-table-alias) — Roman, Mar 13 '11 at 00:39

score 3 · Accepted Answer · answered Mar 13 '11 at 00:34

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.

Seeing it formatted like this let me understand it immediately. Thanks. — John Smith, Mar 13 '11 at 00:53

score 2 · Answer 2 · answered Mar 13 '11 at 00:34

2

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

answered Mar 13 '11 at 00:34

Jaroslav Jandek

9,463
1
28
30

score 2 · Answer 3 · answered Mar 13 '11 at 00:35

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.

score 1 · Answer 4 · answered Mar 13 '11 at 00:34

"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

score 0 · Answer 5 · answered Mar 13 '11 at 00:34

0

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.

answered Mar 13 '11 at 00:34

bobs

21,844
12
67
78

Wayne · Answer 6 · 2011-03-13T00:41:30.023

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

score 0 · Answer 7 · answered Mar 13 '11 at 00:35

0

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 ()

answered Mar 13 '11 at 00:35

Chris

54,599
30
149
186

Can someone explain this SQL query to me?

7 Answers7