SQL IN List – Multi-value comparison IN List
SQL IN list
SQL IN Statement (IN lists) can only process and validate 1000 values. Everything about it returns an error. Here’s an example to demonstrate an IN list. Here only employees and workers are shown, which identification number is in the IN list.
SELECT * FROM emp WHERE 1=1 AND empNo IN (1, 2, ... <max 1000 words>);
Often such statements can simply be avoided. Let’s think of a big company that wants to spend all its production staff. For example, you can refer to the “Departments” table and select the Production department there. However, the case is problematic with an UPDATE or DELETE statement. What should be done when, for example, many (but specific and not all) rows need to be updated?
Current Posts
- Happy Birthday in Hawaiian – 3 easy ways
Happy birthday in Hawaiian Happy birthday in Hawaiian means “Hauʻoli Lā Hānau“. This terms are used most often because they are the standard phrases and can always be used. In social networks, but also... - How do you say happy birthday in Yugoslavian?If you look at the official languages of the successor states of Yugoslavia today, Serbo-Croatian has disappeared. Instead, they speak Bosnian, Croatian, Serbian and Montenegrin. Nevertheless, everyone can still understand everyone. Happy birthday in...
- Fallout 76 – Vendor – Great tipsYou thought Fallout 76 only had train station vendors? Far from it. There are many different trading systems in Fallout. The most well-known system is of course the handle with bottle caps, but there...
Multi Value IN List
For the problem a larger IN list is needed. So if a comparison between several values take place, then another function is necessary. SQL has implemented a new function that can handle larger lists. This feature is younger and thus implemented for a larger comparison.
The new function works in principle the same as the old function, but uses two or more parameters (=multi value IN list). In order to be able to use the new function, two or more parameters (comparison values) must be entered. Below is a simple example, which continues to compare the numbers from the list. Here the first comparison value (1 = 1?) and then the second comparison value (empNo = 1, 2, 3, …?) are compared. In this example, the first comparison value is always correct (true), whereby only the second comparison value is considered. Thus, the simple IN list functionally equivalent in an extended IN list was rewritten.
SELECT * FROM emp WHERE 1=1 AND (1,empNo) IN ((1, 1), (1,2));
Note: Often the beginning or end bracket of the IN list are forgotten.
A simple IN-list and an IN-list for comparing multiple values can be easily implemented with a macro using Notepad ++. Here, the corresponding data are quickly converted from a database or Excel into an in-list. This IN list can then be copied into your SQL script.
