Go to Top

Are you IN or are you out? (Not IN)

This is a part one of two-part on how you can make your queries much more powerful. In first part, we look at whether we’re in or not. Part two can be found here.

The IN clause is a great tool to have in your arsenal, it can allow your forms to be editable or filter forms using .

What is the IN clause?

The IN clause is a member of the SQL language syntax and can be used in your WHERE clause to specify which data to include or exclude. (There are other uses too, as discussed here).

Two Syntax Alternatives

You can use the IN clause by specifying the criteria to include in your query. For example, let’s say you want a list of all orders in IL, WI and MN. You could do it this way:

The problem with the above statement is adding a ton of states makes for a long SQL Statement. Instead you can use the IN clause as a shortcut for the equivalent expression above:

Power Up

But what if you don’t want orders from those states? You can combine IN with NOT like this:

Super Charge

The above examples are great ways to use the IN clause, but you can Super Charge your code by using a select statement instead of listing values like this:

Notice how I dropped the INNER JOIN from my SELECT statement and instead I’m using the IN clause to retrieve all orders for those customers who belong in the states in question.

Use IN to query on tables not in the select statement

There are many situations where you need to avoid a JOIN on your select, here are two:

  • You need users to edit data in your form and using a JOIN will make it read only.
  • One or more tables are not stored together. For example: the Order table might be on SQL Server and the Customer table stored locally in Access. Such a join would be slow and not optimal.

Use IN to filter your form

One of my favorite techniques is to filter a form using the WHERE clause of the DoCmd.OpenForm statement. For example, the other day I was building a dashboard for a banking client that included a count of delinquent loans in the system. I made the number clickable, allowing users to launch the frmLoans form with the list of delinquent loans with the following line:

Note that the data is contained in a SQL Server view that’s linked in Access front-end. Instead of joining the view which may cause sub-optimal performance, the IN clause ensure that the list of LoanIDs is evaluated once.

Use IN to avoid duplicates in your queries that has a GROUP BY clause

Many times you will have a query that contains a GROUP BY clause for which you need unique records, but you are getting multiple records due to joins with other tables. Consider using the IN clause instead of joining on one or more tables.

So are you IN or are you OUT?

Stay tuned for the part two!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

10 Responses to "Are you IN or are you out? (Not IN)"

  • Frank R. Ruperto
    December 23, 2013 - 9:40 pm Reply

    IN/NOT IN clauses are very useful for evaluating more than one value, and against another table via a subquery. However, the performance cost is similar to using the OR clause.

    • Ben Clothier
      December 30, 2013 - 9:09 pm Reply

      Frank, that’s because the IN() is actually a compile-time shortcut for the ORs. Internally, the engine will expand a “x IN(a,b,c)” into a series of “x = a OR x = b OR x = c” . That is why IN([A_comma_delimited_list_of_values_typed_in_a_parameter]) will never work because the parameter is evaluated as a single value, and not parsed by the engine when executing into individual values and evaluated separately. Thus, we have to advise people to use VBA to construct the string dynamically before actually executing it as a SQL like this: “SELECT * FROM y WHERE x IN(” & myVBAVariableContainingCommaDelimitedList & “);”.

      Thanks for commenting!

  • Guy Van Dyck
    September 28, 2013 - 5:33 pm Reply

    My experiences are that NOT IN is very slow in most cases.

    • Guy Van Dyck
      September 28, 2013 - 5:35 pm Reply

      You can use a LEFT JOIN and do test WHERE ISNULL(Fieldname of right table) instead of NOT IN

      • Isaac
        September 28, 2013 - 9:52 pm Reply

        “IsNull(field)”

        Wrong syntax for jet SQL. Isnull is for vba or other rdbms.

  • Isaac Pisors
    September 25, 2013 - 11:58 pm Reply

    The best thing about IN() (IN my opinion) rather than a straight join is that in many cases I use them in pass through queries, so my access database has a PT query that runs sql server sql scripting. As you know, it’s not possible to join to (or refer in any way to) a table that’s not on the server.

    Therefore, when coding for this scenario, I have a saved pass through query with all of the SQL correct, except a dummy placeholder for In, like WHERE [SOMEFIELD] IN ‘PIZZA’

    When I run my VBA script, I:
    1. declare string var to hold sql
    2. populate it with the saved query’s querydef object’s .SQL
    3. use Replace() to replace dummy ‘pizza’ with a long IN() (sometimes containing hundreds and hundreds of values from a form or recordset or whatever the particular process requires)
    4. then run the query.

    In() is beautiful in this scenario and works well with VBA scripting in an access database. The manipulation-abilities of vba…..added to SQL server scripting = Best product!

  • Anders Ebro
    September 23, 2013 - 7:18 pm Reply

    I was about to ask the same question as George has, as to what impacts using In() and (Not In) has, especially on large recordsets.

    Also, I get told the video is not available (Could be a regional thing)

    • Juan Soto
      September 23, 2013 - 10:19 pm Reply

      I understand all of your concerns, frankly, if a join works for me than that’s what I would use first. Otherwise I will use the IN clause as needed.

      Glad you liked the post!

  • George Hepworth
    September 23, 2013 - 5:41 am Reply

    While some of the uses you list for the In() clause make sense, I wonder what impacts you’ve seen on performance with this approach. It’s been my experience that In(), and especially Not In() are less efficient than a straight join.

  • Ruud Jeursen
    September 23, 2013 - 4:18 am Reply

    Thanks Juan, great post. Consider me all IN!! 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us