In Operator SQL

IN Operator SQL

OK I have found the answer

SELECT n FROM Nums    
WHERE n NOT IN (1, 2, null)

evaluates to

SELECT n FROM Nums  
n!=1 AND n!=2 AND n!=null

The outcome of last comparison will always be UNKNOWN.

and the truth table of AND shows that as soon as one Unknown is invloved in it (U,T)(U,F),(U,U) the reult can only be U or F (U=Unknown, F=False) and hence it will not be included in the result set.

In case of

SELECT n FROM Nums
WHERE n IN (1, 2, null) 

equates to

SELECT n FROM Nums
WHERE n = 1 OR n =2 OR n=null

Now for the row with n=1, the operation n=1 will come as true

and for the row with n=2, the operation n=2 will come as true

and for all rows n=null will be unknown

So it gives 1 and 2 in the result set.

Hope u people liked it.

CAN ANYONE PLEASE MARK MY REPLY AS ANSWER

The use of IN operator in a SQL query

As suggested by user eggyal (see commentaries above), I have tried

SELECT * FROM {tables - relationships among tables } WHERE [...] model IN (125, 126, 127) GROUP BY {product} HAVING COUNT(DISTINCT model) = 3

with success.

sql IN operator with text array

For Searching in Array DataType you can use the ANY()

SELECT id FROM table WHERE 'Moscow' = ANY(location);

Live Demo

http://sqlfiddle.com/#!17/a6c3a/2

SQL Server using IN operator with subquery seems to be a BUG?

Since f1 isn't prefixed with tablex and is not in tablex it's bound to f1 from table1. And of course table1.f1 is in (table1.f1).

That's not a bug, that's how binding works in SQL. See "Subqueries (SQL Server)" - "Qualifying column names in subqueries":

(...) If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query. (...)

And this is a good example why making a habit of always qualifying columns can be useful -- at least when there's more than one table involved (by subqueries, joins, etc.). Try

... in (select tablex.f1 from tablex)

and you'll get the error you expect.

You can also use table aliases to shorten the qualified columns, as in:

... in (select x.f1 from tablex x)

How to deal with SQL IN Operator returning multiple values when only one is needed?

If it is not matter what ParentKey will be used, you can use MIN (MAX) function:

SELECT 
    TBL.NodeKey,
    PK AS ParentKey,
    TBL.Child, 
    TBL.Parent
FROM TBL
LEFT JOIN (
    SELECT Child, MIN(NodeKey) PK FROM TBL GROUP BY Child
) P ON P.Child = TBL.Parent;

Test MS SQL query

or another version:

SELECT 
    TBL.NodeKey,
    MIN(P.NodeKey) AS ParentKey,
    TBL.Child, 
    TBL.Parent
FROM TBL
LEFT JOIN TBL P ON P.Child = TBL.Parent
GROUP BY TBL.NodeKey, TBL.Child, TBL.Parent;

Result:

+=========+===========+=======+========+
| NodeKey | ParentKey | Child | Parent |
+=========+===========+=======+========+
| 1       | (null)    | A     | (null) |
+---------+-----------+-------+--------+
| 2       | (null)    | B     | (null) |
+---------+-----------+-------+--------+
| 3       | 1         | C     | A      |
+---------+-----------+-------+--------+
| 4       | 2         | C     | B      |
+---------+-----------+-------+--------+
| 5       | 3         | D     | C      |
+---------+-----------+-------+--------+

IN operator with a very large list is too slow in SQL Server. What should I use instead?

The key thing here is to get the IDS out of the query text SQL. You can pass the IDS in JSON or XML, or a Table-Valued Parameter, or bulk load a temp table. All will be much faster.

EG this

use AdventureWorks2017
set nocount on
dbcc freeproccache
go

declare @ids varchar(max) = ( select STRING_AGG(cast(salesorderid as varchar(max)),',') from sales.SalesOrderHeader )

select @ids 

select count(*)
from 
sales.SalesOrderheader

declare @sql nvarchar(max) = '
select * 
from sales.salesorderheader
where SalesOrderID in (' + @ids + ')'

print '---------IN list---------'
set statistics time on
exec (@sql)
set statistics time off
print '---------IN list---------'

print '---------JSON array---------'
set statistics time on
select * 
from sales.salesorderheader
where SalesOrderID in ( select value from openjson('[' + @ids + ']') )
set statistics time off
print '---------JSON array---------'

outputs

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
---------IN list---------
SQL Server parse and compile time: 
   CPU time = 11781 ms, elapsed time = 12115 ms.

 SQL Server Execution Times:
   CPU time = 657 ms,  elapsed time = 1453 ms.

 SQL Server Execution Times:
   CPU time = 12438 ms,  elapsed time = 13569 ms.
---------IN list---------
---------JSON array---------

 SQL Server Execution Times:
   CPU time = 656 ms,  elapsed time = 984 ms.
---------JSON array---------

To use this method from C# is super-simple. Just serialize an array or list into a string, and pass it in a SqlParameter. EG:

var con = new SqlConnection("Server=localhost;database=adventureworks2017;integrated security=true");
con.Open();

var ids = Enumerable.Range(1, 50_000).ToList();

var cmd = con.CreateCommand();
cmd.CommandText = "select * from sales.SalesOrderHeader where salesorderid in (select value from openjson(@pIds))";
var pIds = cmd.Parameters.Add("@pIds", SqlDbType.NVarChar);

pIds.Value = JsonSerializer.Serialize(ids);

using (var rdr = cmd.ExecuteReader())
{
        while (rdr.Read())
        {
            //do whatever
            Console.Write(".");
        }
}

in operator inside if condition and where clause in sql server

Just use regular boolean expressions:

where (@input = 'web' and col6 in (18, 19, 20, 22)) or
      (@input = 'net' and col6 in (37, 69, 26, 25)) or
      (@input = 'com' and col6 in (55, 66, 46, 27)) or
      (@input not in ('web', 'net', 'com') and col6 in (55, 46, 46, 17))

SQL query having two IN operator with comparison being performed index wise

Not sure if I understand the question correctly. Assuming both lists have the same length, should it be simple like this?

Edit: you can use Q objects to combine your filters

from django.db.models import Q

q_obj = Q()
for i in range(len(list1)):
    q_obj |= Q(column1=list1[i]) & Q(column2=list2[i])
    
result = Table.objects.filter(q_obj )

---

Related Topics