CODEWITHSUNDEEP
mysqlsqldatabase
Ted
Ted

Reputation: 3875

a comma between SELECT statements

I have this query:

SELECT (@a:=@a+1) AS priority
FROM (SELECT t1.name FROM t1 LIMIT 100) x, (SELECT @a:=0) r

a few questions:

1 - What is the comma doing between the SELECTS? I have never seen a comma between commands, and I don't know what it means
2 - why is the second SELECT given a name?
3 - why is the second SELECT inside brackets?
4 - Performance-wize: Does it select the first 100 rows form t1, and then assigns them a number? What is going on here??

Upvotes: 2

Views: 1018

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

It is performing a CROSS JOIN (a cartesian product of the rows) but without the explicit syntax. The following 2 queries produce identical in results:

SELECT *
FROM TableA, TableB

SELECT *
FROM TableA
CROSS JOIN TableB

The query in the question uses 2 "derived tables" instead. I would encourage you to use the explicit join syntax CROSS JOIN and never use just commas. The biggest issue with using just commas is you have no idea if the Cartesian product is deliberate or accidental.

Both "derived tables" have been given an alias - and that is a good thing. How else would you reference some item of the first or second "derived table"? e.g. Imagine they were both queries that had the column ID in them, you would then be able to reference x.ID or r.ID

Regarding what the overall query is doing. First note that the second query is just a single row (1 row). So even though the syntax produces a CROSS JOIN it does not expand the total number of rows because 100 * 1 = 100. In effect the subquery "r" is adding a "placeholder" @a (initially at value zero) on every row. Once that @a belongs on each row, then you can increment the value by 1 for each row, and as a result you get that column producing a row number.

Upvotes: 1

rghome
rghome

Reputation: 8819

  1. x and r are effectively anonymous views produced by the SELECT statements. If you imagine that instead of using SELECTs in brackets, you defined a view using the select statement and then referred to the view, the syntax would be clear.
  2. The selects are given names so that you can refer to these names in WHERE conditions, joins or in the list of fields to select.
  3. That is the syntax. You have to have brackets.
  4. Yes, it selects the first 100 rows. I am not sure what you mean by "gives them a number".

Upvotes: 1

Related Questions