0

I came across this SQL query and am not able to understand how is it working.

SELECT answer AS answer
FROM
  (SELECT answer
   FROM "default"."enriched-responses-dev") AS virtual_table
LIMIT 1000;

And how is it different from this?

SELECT answer AS answer
FROM "default"."enriched-responses-dev"
LIMIT 1000;
Arpit Shukla
  • 9,612
  • 1
  • 14
  • 40
  • When the dbms attempts to interpret your first query, it first generates the output of `SELECT answer FROM "default"."enriched-responses-dev"` as a temporary table, then uses this table to generate the final output. In your second query, no temporary table is generated, no intermediate results to be interpreted, hence it is more efficient. – lemon Jun 21 '22 at 12:39
  • 1
    In SQL the two queries are equivalent, and modern RDBMS probably generate the same execution plan for both. The queries select 1000 answers from the table. If there are more than 1000 rows in the table, the rows get picked arbitarily. This may be different in ElasticSearch, which is a NoSQL DBMS with a language that is not SQL but much like it. So, if you want an answer for ElasticSearch, you may want to remove the SQL tag and rename "SQL query" to "ElasticSearch query" in your title and request. I don't know whether the answers you got actually refer to SQL or ElasticSearch. – Thorsten Kettner Jun 21 '22 at 12:52

2 Answers2

0

I think the second SQL statement is more efficient because there is no need for a temporary result. As far as the output is concerned, both will return the same result.

There is no need for a sub-query in this instance.

It can be simplified to:

SELECT answer
FROM "default"."enriched-responses-dev"
LIMIT 1000;
Nick
  • 3,454
  • 6
  • 33
  • 56
0

this code select "answer" column from "default" database and "enriched-responses-dev" table and named it as "virtual_table" then from this table select "answer" column and named it as answer (and in fact if you don't use this part the result is same as when you are using this part) and this selection limited to last 1000 row SELECT answer AS answer FROM (SELECT answer FROM "default"."enriched-responses-dev") AS virtual_table LIMIT 1000;

but this code:

SELECT answer AS answer
FROM "default"."enriched-responses-dev"
LIMIT 1000;

just select answer column from "enriched-responses-dev" this table from this "default" database and limit it to 1000 last rows

mohsen Rostami
  • 1
  • 3