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?

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.

BEDSURE Ärmeldecke Anthrazit Kuscheldecke mit Ärmeln 170x200 cm, Sweatshirt Decke Ärmel zum Anziehen Erwachsene, Ganzkörperdecke mit Ärmel tragbar extra weich als TV Decke

 (2107)

23,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Samsung Galaxy Watch Active, Bluetooth Fitnessarmband Für Android, Fitness-Tracker, 40 mm,wassergeschützt , Grün (Deutche Version)

 (317)

104,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

AUKEY Bluetooth Kopfhörer, In Ear Kopfhörer Kabellos mit Intensivem Bass, USB-C Quick Charge, 30 Std. Laufzeit, 10mm Dynamic Driver Hi-Fi Sound, Integriertem Mikrofon, IPX6 Wasserdicht, Bluetooth 5

 (9701)

33,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Magma Kissen Lieblingsmensch weiss 30x50cm Made in Germany

 (3608)

15,29 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Vielleicht - Eine Geschichte über die unendlich vielen Begabungen in jedem von uns: Das besondere Kinderbuch (Geschenkbuch Mädchen und Jungen)

 (7513)

12,95 € (as of 15. April 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Apple iPhone 11 (64 GB) - (Product) RED

 (79)

638,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

100 Dinge die ein Junge wissen muss

 (2898)

4,99 € (as of 15. April 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Babyalbum - UNSER ERSTES GEMEINSAMES JAHR: Die schönsten Momente und Erinnerungen - ein bezauberndes Erinnerungsalbum zur Geburt (PAPERISH Babybuch) (PAPERISH Kinderbuch)

 (2436)

14,95 € (as of 15. April 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

cloer 1627-13 Waffelautomat für klassische Herzchen-Waffeln / 930 W/Waffelgröße 15,5 cm/stufenlos wählbarer Bräunungsgrad, Blau

 (9681)

29,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

HP 301 Schwarz Original Druckerpatrone

 (22951)

18,25 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)