CODEWITHSUNDEEP
sqlexists
raghavendra v
raghavendra v

Reputation: 107

SQL queries - difference between exists and in

Do both the following queries always give the same results? If not, why not?

1)

select PayFrequencyTypeID, PayFrequencyDesc 
from db_uspaybo.tblpayfrequency 
where (PayFrequencyTypeID,1) not in (
   select payfrequencytype,1 
   from tblcustomerpayapproval 
   where fedtaxid='903008887' and payrollyear=year(curdate())
);

2)

select payfrequencytypeid 
from tblpayfrequency 
where not exists (
  select distinct payfrequencytype 
  from tblcustomerpayapproval
);

Thanks in advance.

Upvotes: 0

Views: 437

Answers (5)

MaryamAyd
MaryamAyd

Reputation: 151

I think this is what you want with 'Not Exists' A.Id and B.Id here is the Pk and FK that connect these tables.(I don't know the exact field name)

select payfrequencytypeid from tblpayfrequency A where not exists ( select *
from tblcustomerpayapproval B where A.Id=B.Id and B.fedtaxid='903008887' and B.payrollyear=year(curdate()));

Upvotes: 2

MaryamAyd
MaryamAyd

Reputation: 151

The first one returns the some data from db_uspaybo.tblpayfrequency where their PayFrequencyTypeID is not in the payfrequencytype table with those conditions.

The second one does not return any data from db_uspaybo.tblpayfrequency.

Upvotes: 1

user unknown
user unknown

Reputation: 36229

Simplified and tagged:

SELECT p, d 
FROM f 
WHERE (p, 1) NOT IN 
    (SELECT t, 1 
    FROM a 
    WHERE i='903008887' 
    AND y = year (curdate()));


SELECT p 
FROM f 
WHERE NOT EXISTS 
    (SELECT DISTINCT t 
    FROM a);

The result will not be the same. Reason: The first query asks for 2 columns. But we cannot know wheter the question for the year has some effect, or the filtering for 903008887. If that filtering has an effect - how could it be done by the second query?

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

Basically not in and not exists are very similar and usually yield the same result.

A difference is that in will return false if one of the values in the set is NULL (at least, it does on Oracle), while exists only checks for existance of a record, unregarding its values.

In this specific case, you got a WHERE clause that will cause the first query to return a different result.

A third approach which is generally faster on MySQL, is to left join the table in the main query and check if the join field is NULL:

select payfrequencytypeid 
from 
  tblpayfrequency f
  left join tblcustomerpayapproval a
    on a.payfrequencytype = f.payfrequencytype
where
  a.payfrequencytype IS NULL

Other general tips:

  1. You can skip the 1 of course.
  2. You don't need the DISTINCT in the second query. You allow the database to choose the best optimization path if you remove that.
  3. Not exists is often faster in regards to in, although this also depends on the optimization path chosen by the database. You should really try this on a live server and live data to be sure.

Upvotes: 1

Azat
Azat

Reputation: 2335

Look at the following blog:

IN Vs Exist in SQL

Alternatively you can always google for such type of questions.

Upvotes: 0

Related Questions