Null restriction in sql in operator

Question

I'm looking for a query to fetch multiple column data for many rows at once.

Table 1. No null value

customer, price1, price2, price3, ..., price10
Tata,     100,    200,    300,    ..., 1000
Ford,     111,    222,    333,    ..., 1388

I can use below query:

SELECT *
FROM table1
WHERE (customer,price1,price2,price3,...~price10)
IN (
(Tata,100,200,300,400,500,..~1000)
OR
(Ford,111,222,333,444,555,...~1388)

Table 2:

consumer_code, price1, price2, price3, price4, …, price10
Chn,           100,    200,    (null), (null), …, 600
Hyd,           121,    378,    262,    (null), …, (null)

SQL where in operator doesn't accept null values. Any suggestions for a better query?

I need to pass bulk data say >500 or 1000 at once so as to reduce multiple db operations by using multiple select statements and improve the retrieval speed.

I don't get the logic - are you saying you only want rows where there is an exact match so for example if chn had 20 in price 3 it should not appear or are you saying if all of the non null columns match then the row should appear? — P.Salmon
– P.Salmon, Commented Aug 28, 2019 at 10:25
Note that a database table is not a spreadsheet. Seriously consider whether your present schema is fit for purpose. (Hint: it isn't) — Strawberry
– Strawberry, Commented Aug 28, 2019 at 11:49
@P.Salmon Yes, I need the rows where there is an exact match. Combination of all the columns — Dushyanth Sunny
– Dushyanth Sunny, Commented Aug 28, 2019 at 12:19
@strawberry my schema need to support this. I need to pass spreadsheet values and validate the results. — Dushyanth Sunny
– Dushyanth Sunny, Commented Aug 29, 2019 at 6:35

Gordon Linoff · Accepted Answer · 2019-08-28 10:56:47Z

1

Sql where in operator doesn't accept null values. Any suggestions for a better query?

The in operator does accept NULL values. It simply does not consider them to be equal.

You can use the NULL-safe comparison, <=> instead of IN:

where (customer_code <=> 'Tata' and price1 <=> 100 and . . .
      ) or
      (customer_code <=> 'Ford' and price1 <=> 111 and . . .
      ) or

You can include NULL for the values.

This also works with tuples, so:

where (customer, price1, price2, price3,... ~price10)
<=> ('Tata', 100, 200, 300, 400, 500,.. ~1000) or
      (customer, price1, price2, price3,... ~price10) <=> ( . . . ) or
       . . .

answered Aug 28, 2019 at 10:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dushyanth Sunny Over a year ago

Hi Lintoff It almost similar to where( (customer = 'Tata', price1=100, price2=200,price3=300) or (customer='Tata',price1 IS NULL, price2 IS NULL, price3=400) ). Your query would be good if we mention columns at once and values as tuples

P.Salmon · Accepted Answer · 2019-08-28 10:45:08Z

1

Assuming you want an exact match to your input you could use a sub query for the input and join to the main table

drop table if exists t;

create table t
(mid varchar(3), p1 int,p2 int,p3 int);

insert into t values
('frd',1,2,3),
('frd',1,2,5),
('frd',1,null,3),
('chn',1,2,3);

select t.* 
from t
join (select cast('frd' as char(3)) mid, 1 p1,null p2,3 p3
        union
        select 'chn',1,2,3) s 
on  s.mid = t.mid and
        coalesce(s.p1,-1) = coalesce(t.p1,-1) and 
        coalesce(s.p2,-1)   = coalesce(t.p2,-1) and 
        coalesce(s.p3,-1)   = coalesce(t.p3,-1);


+------+------+------+------+
| mid  | p1   | p2   | p3   |
+------+------+------+------+
| frd  |    1 | NULL |    3 |
| chn  |    1 |    2 |    3 |
+------+------+------+------+
2 rows in set (0.00 sec)

You may get collation issues. It might be easier long term to create a table to hold your conditions and use that instead of a sub query.

answered Aug 28, 2019 at 10:45

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

1 Comment

Dushyanth Sunny Over a year ago

It works. Thanks for your contribution. But I didn't get the point about collation issues and how creating a separate table would help. If possible could you explain in brief or any link which provides related info. Is it safe to pass too many inner select values say around 500 at once?

Collectives™ on Stack Overflow

Null restriction in sql in operator

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related