Many times we have seen SELECT 1 from a table as subqueries or a query in itself. This article will discuss what select 1 from a table means in MySQL and where it is used.
Table of Contents:-
Let us get started by making the sample data to be used across. We will be creating two tables, student_address_city and student_college_details, and inserting some rows into them.
#create the table student_address_city CREATE TABLE student_address_city ( student_id int NOT NULL, city varchar(255) DEFAULT NULL ); #insert rows to student_address_city INSERT INTO student_address_city (student_id,city) VALUES (1,"Santiago"), (2,"Chicago"), (3,"Washington"), (4,"New York"), (5,"Chicago"), (6,"Washington"); #create the table student_college_details CREATE TABLE student_college_details ( student_id int NOT NULL, student_college_name varchar(255) DEFAULT NULL ); #insert rows into student_college_details INSERT INTO student_college_details (student_id,student_college_name) VALUES (1,"XVR Academy"), (2,"XVR Academy"), (3,"XVR Academy"), (4,"Learner's Institute"), (5,"Learner's Institute");
To view the snapshot of both the tables execute:
SELECT * FROM student_address_city;
Output:-

SELECT * FROM student_college_details;
Output:-

MySQL: What is SELECT 1 from a table?
Select 1 will return the value 1 for all the rows in a table. For example
Frequently Asked:
- How to get MySQL version
- MySQL SELECT WHERE NOT IN
- MySQL check if table exists
- MySQL Select last N rows
SELECT 1 FROM student_address_city;
Action Output Message:-
6 row(s) returned
Output:-

6 rows are returned with value 1 in each row; this is the number of rows present in the table student_address_city. Let us add some conditions as well and then select 1.
SELECT 1 FROM student_address_city where city = "Chicago";
Action Output Message:-
2 row(s) returned
Output:-

We will select the city column as well in the above query for more clarity.
SELECT 1,city FROM student_address_city where city = "Chicago";
Action Output Message:-
2 row(s) returned
Output:-

Where is SELECT 1 from a table/query used?
SELECT 1 is usage:
- Some databases use the SELECT 1 query to view if the connection is still alive. Since the result is 1 for each row, no one bothers to view the results.
- SELECT 1 also used where we want to check the existence of rows in a subquery. Let us see an example for better understanding.
Example: Get all the details from the student_address_city table only if there is any college named ‘XVR Academy’ present in the student_college_name table.
SELECT * FROM student_address_city WHERE EXISTS( SELECT 1 FROM student_college_details WHERE student_college_name = 'XVR Academy');
Action Output Message:-
6 row(s) returned
Output:-

We got the results because rows in the table student_college_details existed with student_college_name = ‘XVR Academy.’ Let us run another query to verify.
SELECT * FROM student_address_city WHERE EXISTS( SELECT 1 FROM student_college_details WHERE student_college_name = 'ABC');
Action Output Message:-
0 row(s) returned
Output:-

No row is returned as student_college_details table does not have any data with student_college_name = ‘ABC’
READ MORE
- MySQL select first row in each group
- MySQL select first row
- MySQL Select last N rows
- MySQL select last record of table
We hope this article helped understand SELECT 1 in MySQL queries and its usage. Good Luck!!