.NET Answers

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:

SELECT * FROM myTable
  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:

SELECT * FROM myTable
  WHERE ID IN (1,1,5)

You could also use this with strings:

SELECT * FROM myTable
  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:

SELECT * FROM myTable
  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.

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

Other Related Items:

Mac OS X Snow Leopard: The Missing Manual

For a company that promised to "put a pause on new features," Apple sure has been busy-there's barely a feature left untouched in Mac OS X 10.6 "Sn... Read More >

SQL Server 2000 for Developers Part Three (Complete Set)LearnKey's SQL Server 2000 for Developers Part 3 provides comprehensive instruction for utilizing SQL to its fullest potential. Experts Mary Chipman a... Read More >

Maximum Fitness Gear All-In-One Doorway Chin Up Bar with Bonus Top 20 Exercises To Six Pack Abs GuideThe best, fully functional, all-in-one pull up/chin up bar in the market today. Professional exercise bar provides maximum durability and is made of h... Read More >

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Related Post

• Basic SQL Commands – SQL For Programmers
• SQL SELECT CASE Instead of IIF
• SQL – Filtering WHERE condition on two rows
• Random in SQL – SQL For Programmers
• SQL CURSOR Performance – SQL For Programmers

Bookmark to:

Hide Sites

Comments are closed.