SQL IN List – Multi-value comparison IN List
Inhaltsverzeichnis
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.
Das Café am Rande der Welt: eine Erzählung über den Sinn des Lebens
8,95 € (as of 17. October 2021 15:22 GMT +02:00 - 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 Buds Live, kabellose Bluetooth-Kopfhörer mit Noise Cancelling (ANC), komfortable Passform, ausdauernder Akku, Wireless Kopfhörer in blau
79,99 € (as of 17. October 2021 15:23 GMT +02:00 - 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.)
Sheepworld 45140 Tasse mit Motiv-Druck Ohne Bruder ist alles doof, Porzellan, Geschenk Bruder, 45 cl
7,99 € (as of 17. October 2021 15:22 GMT +02:00 - 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.)
De'Longhi Dedica Style EC 685.BK Espresso Siebträgermaschine, Espressomaschine mit Professioneller Milchschaumdüse, nur 15 cm breit, 1 Liter Wassertank, Vollmetallgehäuse, E.S.E Pads geeignet, beige
125,20 € (as of 17. October 2021 15:22 GMT +02:00 - 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.)
