Werbung / Advertisements

SQL IN List – Multi-value comparison IN List

SQL IN Liste Multi Value

SQL IN list

Werbung / Advertisements

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

  • 10 ways to wish Spaniards a happy birthday
    Werbung / Advertisements Birthdays in Spain are celebrated with much warmth and joy, reflecting the country’s vibrant culture and deep-rooted traditions. Understanding the Spanish way of celebrating can turn a simple birthday wish into...
  • 10 ways to wish Swiss people a happy birthday
    Werbung / Advertisements Switzerland, with its rich tapestry of cultures and languages, offers a unique blend of traditions, especially when it comes to celebrating birthdays. Understanding the cultural nuances behind birthday wishes can transform...
  • Navigating Kotor: Tips and Tricks for First-Time Visitors
    Welcome to Kotor Werbung / Advertisements Nestled along the Adriatic coast, Kotor, Montenegro, is a picturesque city that beckons travelers with its blend of historic charm, breathtaking landscapes, and rich cultural heritage. A UNESCO...

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));
Werbung / Advertisements

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.

You may also like...

Werbung / Advertisements