How this query can be answered ? Select SUM(1) FROM table-CodePudding

select * from "Test"."EMP"

Select SUM(1) FROM "Test"."EMP";
Select SUM(2) FROM "Test"."EMP";
Select SUM(3) FROM "Test"."EMP";

why the output of these queries is?

5
10
15

And I don't understand why they write table name like this "Test"."EMP"

CodePudding user response：

your table has 5 records. the statement select 1 from test.emp returns 5 records with values as 1 for all 5 records.

id
1
1
1
1
1

This is because db engine simply returns 1 for each existing record without reading the contents of the cell. and same happens for select <any static value> from test.emp

same happens for 2 and 3

id
2
2
2
2
2

hence there are 5 records returned with the static values and sum of those values will be the product of static number passed in the select statement and total records in the table

additional fact: It is always recommended to perform count(1) than count(*) as it consumes less resource and hence less load on the server

CodePudding user response：

I don't think it's "Test"."EMP" with double quotes.. it's probably `Test`.`EMP` with backticks instead. The definition means its database_name.table_name. This is the recommended format to get the correct table_name from database_name; in this case, you're specifically making the syntax to query from `Test`.`EMP`. Read more about identifier qualifiers.

As for SUM(x), the x get's repeated according to the rows present in the table. So SUM(1) on 5 rows is 1 1 1 1 1, SUM(2) on 5 rows is 2 2 2 2 2, and so on.