SELECT 1 FROM TABLE

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:-

• MySQL: What is SELECT 1 from a table?
• Where is SELECT 1 from a table/query used?

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

 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!!