How To SQL Check if at Least One Row Exists

In SQL there are several ways to SQL check if at least one row exists in the table. I hope to list everything. Performance can change according to the pattern of SQL(How To Performance Tuning In Oracle | 10 Tips). I hope to use EXIST, ROWNUM, and FETCH keywords to test this. Also hope to sql check if at least one row exists when you have Group by condition as well.

1. ROWNUM

ROWNUM is not used in all the databases but if it is there then that is the best way to check it. This is used to limit the return record count by adding ROWNUM 5. If ROWNUM 1 then this is the return first row of the result set.

--
SELECT COUNT(*)
  FROM employee_table
 WHERE employee_name like 'Name-5%'
   AND rownum = 1
Result 
Count(*)
1
--

Here ROWNUM looks for the first matching row and as soon as the return count is 1.

2. EXISTS

Exist is used to test whether matching records exist with outer query. Exist return TRUE if one record is found from INNER query. If the record is not found from the INNER query then return FALSE

--
SELECT col
  FROM dual
 WHERE EXISTS( SELECT 1
                 FROM employee_table
                WHERE employee_name like 'Name-%' )

Result 
col
1
--

EXISTS clause Not allow to go through the full table scan because it returns one record is found. The first example looks to perform well compared to the second but both options are good.

3. ROWNUM and EXISTS

This is a mix of ROWNUM and EXISTS doing similar work to the above two examples.

--
SELECT 1
FROM dual
WHERE ROWNUM = 1
AND EXISTS (SELECT 1 FROM employee_table WHERE employee_name like 'Name-%');
Result 
col
1
--

4. FETCH FIRST

This query uses the FETCH FIRST clause to limit the number of records returned by the query. Both queries do the same.

--
SELECT 1 FROM employee_table WHERE employee_name like 'Name-%'
FETCH FIRST 1 ROWS ONLY;

SELECT 1
FROM dual
WHERE EXISTS (SELECT 1 FROM employee_table WHERE employee_name like 'Name-%')
FETCH FIRST 1 ROWS ONLY;
--

5. CASE Statement PLSQL

This example shows how to SQL check if at least one row exists when inside the CASE stamen. Sometimes need to run a block of code if and only at least a single record exists according to the condition.

--
select case 
        when exists (
            select * 
            from employee_table 
            where employee_name like 'Name-%' 
        ) then 1 
        else 0 
    end as RowsExist
--

Sample Code

Below is the sample code for all the examples I mentioned above. This ORACLE code may not work on all the databases but most databases will work.

--
CREATE TABLE employee_table(
    employee_id VARCHAR2(50) ,
    employee_state VARCHAR2(50) ,
    department_id VARCHAR2(50) ,
    employee_name VARCHAR2(100) ,
    PRIMARY KEY(employee_id)
);

declare 
  -- insert 100000 records into database
  i integer:=0;
    type namesarray IS VARRAY(10) OF VARCHAR2(50); 
  names namesarray; 
begin
      names := namesarray('HR', 'PAY', 'MANUF', 'DEP-1', 'DEP-2','DEP-3','DEP-4');
LOOP
   i:= i+1;
   INSERT INTO employee_table (employee_id, employee_state,department_id,employee_name) VALUES ('E-'||i,null,names(MOD(i,7)+1),'Name-'||i);
   EXIT WHEN i > 999999;
END LOOP;
end;
--

There is no single way to SQL check if at least one row exists but there are many but performance-wise. The first two are the best options. So if you have any concerns or ideas other than this please comment below for others’ reference.