SQL IN List – Multi-value comparison IN List

by Blogofant · Published 3. March 2021 · Updated 3. March 2021

Inhaltsverzeichnis

SQL IN list
Current Posts
Multi Value 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

6 ways to wish Russians a happy birthday27. July 2021
7 ways to wish Romanians a happy birthday27. July 2021
Differences between NVL, NVL2 and COALESCE6. May 2021

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.

Click to rate this post!

[Total: 0 Average: 0]