Exists/Not Exists: 'Select 1' VS 'Select Field'

Exists / not exists: 'select 1' vs 'select field'

Yes, they are the same. exists checks if there is at least one row in the sub query. If so, it evaluates to true. The columns in the sub query don't matter in any way.

According to MSDN, exists:

Specifies a subquery to test for the existence of rows.

And Oracle:

An EXISTS condition tests for existence of rows in a subquery.

Maybe the MySQL documentation is even more explaining:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

NOT IN vs NOT EXISTS and select 1 1?

Your first query will get you only top most record (very first record) out of the total rows in result set. So, if your query returns 10 rows .. you will get the first row. Read more about TOP

SELECT TOP 1 FROM tblSomeTable

In your Second query the part under () is a subquery, in your case it's a correlated subquery which will be evaluated once for each row processed by the outer query.

NOT EXISTS will actually check for existence of the rows present in subquery

WHERE NOT EXISTS
(
SELECT TOP 1 1 FROM tblEmployee e2 WHERE e2.E_ID = e.E_ID AND isFired = 'N'
)

Read more about Correlated subquery as well as Subqueries with EXISTS

Which is better select 1 vs select * to check the existence of record?

EXISTS will check if any record exists in a set. so if you are making a SELECT from 1 million records or you are making a SELECT from 1 record(let say using TOP 1), they will have same result and same performance and even same execution plan.(why?) Because exists will not waits until 1 million record scan complete(or 1 record scan complete). Whenever it finds a record in a set, it will be return the result as TRUE(There is no matter in this case you are using * or column name both will have same performance result).

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT TOP 1 * FROM dbo.titleauthor)
PRINT 'b'

below is the execution plan for these queries(as I have Screen size problem, I have cropped it's image)

But this scenario and performance and even execution plan will be completly change, when you are using queries as follow(I do not know why should use this query!):

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT 1 )
PRINT 'b'

in this scenario, as SQL Server does not need to perform any scan operation in second query, then the execution plan will be changed as follow:

SQL insert into, where not exists (select 1... what this 1 stands for?

Exists checks for the presence of rows in the sub-select, not for the data returned by those rows.

So we are only interested if there is a row or not.

But as you can't have a select without selecting something, you need to put an expression into the select list.

That could be any expression. The actual expression is of no interest You could use select some_column or select * or select null or select 42 - that would all be the same.

IF EXISTS (SELECT 1...) vs IF EXITS (SELECT TOP 1 1...)

If you view the execution plan for these queries you can see that they are identical. Good coding practices would ask that you leave out the "TOP 1" but they should run identical either way.

What does it mean SELECT EXISTS (SELECT 1 FROM favoritelist WHERE id=:id)

SELECT EXISTS (SELECT 1 FROM favoritelist WHERE id=:id)

The exists condition evaluates whether at least one row exists in favoritelist where id matches the given parameter. The outer query evaluates the condition in numeric context, and returns 1 if the condition is satisfied, else 0.

In a nutshell:

• if at least one record exists in favoritelist whose id matches the parameter, the query returns 1

• else, it returns 0

Where does the practice exists (select 1 from ...) come from?

The main part of your question is - "where did this myth come from?"

So to answer that, I guess one of the first performance hints people learn with sql is that select * is inefficient in most situations. The fact that it isn't inefficient in this specific situation is hence somewhat counter intuitive. So its not surprising that people are skeptical about it. But some simple research or experiments should be enough to banish most myths. Although human history kinda shows that myths are quite hard to banish.

SELECT TOP 1 1 VS IF EXISTS(SELECT 1

I'd recommend IF EXISTS(SELECT * ...), unless this is actually causing a performance issue. It expresses the intent of the query in a much better understood fashion than alternatives.

I'd avoid COUNT(*) (as in the current answers) unless you actually need the count of rows from the table.

If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:

select 1 where exists(select * from BigTable where SomeColumn=200)

Which produces the same result set as your second query (either 0 or 1 row)

Select query 0=0 Vs not exists

Yes same query.

SELECT * 
  FROM tab1 
 WHERE code = 1 
   AND type = 'A' 
   AND 0 = (SELECT Count(1) 
              FROM tab1 
             WHERE code = 1 
              AND  tr_type = 'APPROVE' 
              AND  security = 'Y') 

If we will take output of inner query in the above query

SELECT Count(1) 
  FROM tab1 
 WHERE code = 1 
   AND tr_type = 'APPROVE' 
   AND security = 'Y'

I am assuming , Record with code = 1 , TR_TYPE = 'APPROVE' and security = 'Y' is present. So output will be 1. (Assumption is only one matching record is present).

SELECT * 
  FROM tab1 
 WHERE code =1 
   AND type='A' 
   AND 0=1 (replacing the result)

So this will not return any data as 0 is never equal to 1.

SELECT * 
  FROM tab1 
 WHERE code = 1 
   AND type = 'A' 
   AND NOT EXISTS (SELECT 1 
                     FROM tab1 
                    WHERE code = 1 
                      AND tr_type = 'APPROVE' 
                      AND security = 'Y' 
                      AND rownum = 1)

Now checking the above query, inner query return 1 as the data with the where clause is present so result will be

SELECT * 
  FROM tab1 
 WHERE code =1 
   AND type='A' 
   AND NOT EXISTS (1)

Exists(If any record found) = true and in our case Exists(1) = true so
Not Exist (1) = false. Which denotes to false as it has 1 record , so it will also not return the data.

So if you are asking that both query will return same output. So It will return same output.

---

Related Topics