[mysql] What does it mean by select 1 from table?

I have seen many queries with something as follows.

Select 1  
From table

What does this 1 mean, how will it be executed and, what will it return?

Also, in what type of scenarios, can this be used?

If you just want to check a true or false based on the WHERE clause, select 1 from table where condition is the cheapest way.

select 1 from table will return the constant 1 for every row of the table. It's useful when you want to cheaply determine if record matches your where clause and/or join.

select 1 from table is used by some databases as a query to test a connection to see if it's alive, often used when retrieving or returning a connection to / from a connection pool.

it does what it says - it will always return the integer 1. It's used to check whether a record matching your where clause exists.

This is just used for convenience with IF EXISTS(). Otherwise you can go with

select * from [table_name]

Image In the case of 'IF EXISTS', we just need know that any row with specified condition exists or not doesn't matter what is content of row.

select 1 from Users

above example code, returns no. of rows equals to no. of users with 1 in single column

Although it is not widely known, a query can have a HAVING clause without a GROUP BY clause.

In such circumstances, the HAVING clause is applied to the entire set. Clearly, the SELECT clause cannot refer to any column, otherwise you would (correct) get the error, "Column is invalid in select because it is not contained in the GROUP BY" etc.

Therefore, a literal value must be used (because SQL doesn't allow a resultset with zero columns -- why?!) and the literal value 1 (INTEGER) is commonly used: if the HAVING clause evaluates TRUE then the resultset will be one row with one column showing the value 1, otherwise you get the empty set.

Example: to find whether a column has more than one distinct value:

SELECT 1
  FROM tableA
HAVING MIN(colA) < MAX(colA);

I see it is always used in SQL injection,such as:

www.urlxxxxx.com/xxxx.asp?id=99 union select 1,2,3,4,5,6,7,8,9 from database;

These numbers can be used to guess where the database exists and guess the column name of the database you specified.And the values of the tables.

If you mean something like

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT 1 FROM table WHERE...) 

then it's a myth that the 1 is better than

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT * FROM table WHERE...) 

The 1 or * in the EXISTS is ignored and you can write this as per Page 191 of the ANSI SQL 1992 Standard:

SELECT * FROM AnotherTable
  WHERE EXISTS (SELECT 1/0 FROM table WHERE...) 

If you don't know there exist any data in your table or not, you can use following query:

SELECT cons_value FROM table_name;

For an Example:

SELECT 1 FROM employee;

1. It will return a column which contains the total number of rows & all rows have the same constant value 1 (for this time it returns 1 for all rows);
2. If there is no row in your table it will return nothing.

So, we use this SQL query to know if there is any data in the table & the number of rows indicates how many rows exist in this table.

This means that You want a value "1" as output or Most of the time used as Inner Queries because for some reason you want to calculate the outer queries based on the result of inner queries.. not all the time you use 1 but you have some specific values...

This will statically gives you output as value 1.

The reason is another one, at least for MySQL. This is from the MySQL manual

InnoDB computes index cardinality values for a table the first time that table is accessed after startup, instead of storing such values in the table. This step can take significant time on systems that partition the data into many tables. Since this overhead only applies to the initial table open operation, to “warm up” a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1

The result is 1 for every record in the table.

To be slightly more specific, you would use this to do

SELECT 1 FROM MyUserTable WHERE user_id = 33487

instead of doing

SELECT * FROM MyUserTable WHERE user_id = 33487

because you don't care about looking at the results. Asking for the number 1 is very easy for the database (since it doesn't have to do any look-ups).

it simple means that you are retrieving the number first column from table ,,,,means select Emply_num,Empl_no From Employees ; here you are using select 1 from Employees; that means you are retrieving the Emply_num column. Thanks