Home » SQL For Programmers » SQL For Programmers – Finding IN a List

SQL For Programmers – Finding IN a List

TSQL - IN In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we want to find a record that matches more than one set of data?

That is what the IN clause is for.

The most common use of the IN clause is:

  WHERE fildName IN (comma,Separated,List)

So if you were looking for the records whose ID were equal to 1, 3, or 5, your query would look like:

  WHERE ID IN (1,1,5)

You could also use this with strings:

  WHERE firstName IN ('Dave','George','Frank')

The final form is using a sub-select.  I should warn you that most DBAs frown on this form since it does have performance issues.  But since most of us are just looking to get a job done, this may be just the ticket.  Given the alternatives, this is a simple way to find all the records in one table that are in another.  If there is a performance problem, then you can go looking for some alternative.

So, here is the syntax:

  WHERE firstName IN
    (SELECT firstName FROM myOtherTable
     WHERE someOtherCondition)

I recently used this in combination with NOT to find fields in the main table that didn’t have corresponding records in the sub-select table.

  WHERE firstName NOT IN
    (SELECT firstName FROM myOtherTable
     WHERE someOtherCondition)

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL For Programmers – Finding a StringSQL For Programmers – Finding a String Many times in our queries, we aren't looking for an exact match.  We are looking for one string that exists in another.  There are a couple statements available to us that will allow us […]
  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.Naturally, as a programmer, I started looking for the SQL […]
  • SQL CURSOR Performance – SQL For ProgrammersSQL CURSOR Performance – SQL For Programmers Last week I showed how to use the SQL CURSOR to loop through records in a database.  In that article I mentioned that you want to avoid using the CURSOR if you can because it has […]
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.There are actually two parts […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.