CODEWITHSUNDEEP
sqlwhere-clause
saleem
saleem

Reputation: 415

sql query "WHERE IN"

I have this query :

SELECT Company_Name FROM tblBeneficaries WHERE BeneficaryID IN ()

When I execute it it returns the message:

Incorrect syntax near ')'.

What is the problem?

Upvotes: 2

Views: 32723

Answers (6)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Your query as it reads should return no rows, because the set of desired BeneficaryIDs is empty. This syntax however is not valid, as the IN clause must contain at least one value, unfortunately. In order to represent an empty set, add NULL:

SELECT Company_Name FROM tblBeneficaries WHERE BeneficaryID IN (NULL);

This is useful when building the query dynamically, where you end up with zero or more elements in the set, i.e. (NULL) for an empty set or (NULL, 4, 16) for a set of numbers 4 and 16, for instance.

It is still an annoying limitation on the IN clause and the trick shown doesn't work with NOT IN! (Adding NULL would prevent you from fetching any record, so you would have to use a non-null value that's never used, such as WHERE BeneficaryID NOT IN (-1)).

Upvotes: 6

Nikhilp Shaju
Nikhilp Shaju

Reputation: 23

SELECT Company_Name
FROM tblBeneficaries
WHERE BeneficaryID IN (1,2,3,4)

Execute like this by replacing the () by your BeneficaryID, if BeneficaryID is INT.
Add '' for the values if BeneficaryID is VARCHAR, like ('1', '2')

Upvotes: 0

Biswabid
Biswabid

Reputation: 1411

You are missing the parameters in the in condition.

please find the below link for the correct way of using WHERE IN :

http://www.w3schools.com/sql/sql_in.asp

Upvotes: 2

Rahul
Rahul

Reputation: 77846

That's because, your IN clause has no parameter/argument WHERE BeneficaryID IN (). It should be WHERE BeneficaryID IN (id1,id2,id3, ...,idn)

Your current query is same as saying below, no need of the WHERE condition

SELECT Company_Name FROM tblBeneficaries

Upvotes: 6

Tharif
Tharif

Reputation: 13971

Syntax of IN SQL :

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

Provide values between () in below query :

SELECT Company_Name FROM tblBeneficaries WHERE BeneficaryID IN ()

Your values are missing that throws you error.

Check demo example here.

Upvotes: 2

Jens
Jens

Reputation: 69439

You need to add values for the IN operator: For instance:

SELECT Company_Name FROM tblBeneficaries WHERE BeneficaryID IN (1,2,3,4)

Read in w3school for the syntax.

Upvotes: 1

Related Questions