The IN (... ) clause

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

The IN (... ) clause

jose isaias cabrera

Greetings!

I know that the IN clause contains a list of something. I.e.

IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')

So the question is, is there a shorter way for one to say something like,

IN ('2014-01-01', ..., '2014-01-05')

where the content of the IN would have the first item and the last item of
the list, but that's it?  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Darren Duncan
On 2014-09-13, 10:07 PM, jose isaias cabrera wrote:

> I know that the IN clause contains a list of something. I.e.
>
> IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
>
> So the question is, is there a shorter way for one to say something like,
>
> IN ('2014-01-01', ..., '2014-01-05')
>
> where the content of the IN would have the first item and the last item of the
> list, but that's it?  Thanks.

You're talking about a range/interval.

In SQL it is spelled like this:

   BETWEEN '2014-01-01' AND '2014-01-05'

-- Darren Duncan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Martin Engelschalk
In reply to this post by jose isaias cabrera
Hi Jose,

you are probably looking for the between-Operator: Open
http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator"

inn you case, date BETWEEN '2014-01-01' AND '2014-01-05'

Martin


Am 14.09.2014 07:07, schrieb jose isaias cabrera:

>
> Greetings!
>
> I know that the IN clause contains a list of something. I.e.
>
> IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
>
> So the question is, is there a shorter way for one to say something like,
>
> IN ('2014-01-01', ..., '2014-01-05')
>
> where the content of the IN would have the first item and the last
> item of the list, but that's it?  Thanks.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
[hidden email]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Simon Slavin-3

On 14 Sep 2014, at 11:59am, Martin Engelschalk <[hidden email]> wrote:

> you are probably looking for the between-Operator: Open http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator"
>
> inn you case, date BETWEEN '2014-01-01' AND '2014-01-05'

By the way, kudos for storing your dates in a format which is subject to ASCII sorting like that.  Assuming that '2014-01-05' means the fifth of January and not the first of May.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Clemens Ladisch
In reply to this post by jose isaias cabrera
jose isaias cabrera wrote:
> I know that the IN clause contains a list of something. I.e.
>
> IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
>
> So the question is, is there a shorter way for one to say something like,
>
> IN ('2014-01-01', ..., '2014-01-05')
>
> where the content of the IN would have the first item and the last item of the list, but that's it?

In this case, BETWEEN works just fine.

In the general case, you can create a series of values with a common table expression:

  WITH RECURSIVE dates(d) AS (
    SELECT '2014-01-01'
    UNION ALL
    SELECT date(d, '+1 day')
    FROM dates
    WHERE d < '2014-01-05'
  )
  SELECT * FROM MyTable WHERE MyColumn IN dates;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

jose isaias cabrera
In reply to this post by Darren Duncan

"Darren Duncan" wrote...

> On 2014-09-13, 10:07 PM, jose isaias cabrera wrote:
>> I know that the IN clause contains a list of something. I.e.
>>
>> IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
>>
>> So the question is, is there a shorter way for one to say something like,
>>
>> IN ('2014-01-01', ..., '2014-01-05')
>>
>> where the content of the IN would have the first item and the last item
>> of the
>> list, but that's it?  Thanks.
>
> You're talking about a range/interval.
>
> In SQL it is spelled like this:
>
>   BETWEEN '2014-01-01' AND '2014-01-05'

Yeah, that is what I am using now.  I was trying to get the speed that
supposedly is in the IN clause. :-)  Thanks.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

jose isaias cabrera
In reply to this post by Simon Slavin-3

"Simon Slavin" wrote...

>
> On 14 Sep 2014, at 11:59am, Martin Engelschalk <[hidden email]>
> wrote:
>
>> you are probably looking for the between-Operator: Open
>> http://www.sqlite.org/lang_expr.html and search for "The BETWEEN
>> operator"
>>
>> inn you case, date BETWEEN '2014-01-01' AND '2014-01-05'
>
> By the way, kudos for storing your dates in a format which is subject to
> ASCII sorting like that.  Assuming that '2014-01-05' means the fifth of
> January and not the first of May.

Thanks.  Yes, I have always done that, even in filenames with dates in them.
But, am pretty sure that I read this in the sqlite site somewhere about
storing dates.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

jose isaias cabrera
In reply to this post by Martin Engelschalk

"Martin Engelschalk" wrote...

> Hi Jose,
>
> you are probably looking for the between-Operator: Open
> http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator"
>
> inn you case, date BETWEEN '2014-01-01' AND '2014-01-05'
>
> Martin

Thanks, Martin.  Yes, that is what I am using now... :-)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Simon Slavin-3
In reply to this post by jose isaias cabrera

On 14 Sep 2014, at 8:58pm, jose isaias cabrera <[hidden email]> wrote:

> "Darren Duncan" wrote...
>>
>>
>>  BETWEEN '2014-01-01' AND '2014-01-05'
>
> Yeah, that is what I am using now.  I was trying to get the speed that supposedly is in the IN clause. :-)  Thanks.

BETWEEN is fast than IN for any sensible number of IN arguments.  BETWEEN gets directly translated to like so

A BETWEEN B AND C ==> A >= B AND A <= C

IN has to check whether A matches any one of a number of arguments.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

Richard Hipp-3
On Sun, Sep 14, 2014 at 4:31 PM, Simon Slavin <[hidden email]> wrote:

>
> On 14 Sep 2014, at 8:58pm, jose isaias cabrera <[hidden email]>
> wrote:
>
> > Yeah, that is what I am using now.  I was trying to get the speed that
> supposedly is in the IN clause. :-)  Thanks.
>
> BETWEEN is fast[er] than IN....f
>

Confirmed.  Just to verify this, I ran the following script which times the
same query using both IN and BETWEEN:

.open /home/drh/www/repos/tcl.fossil
CREATE TEMP TABLE t1(id INTEGER PRIMARY KEY, date TEXT);
INSERT INTO t1 SELECT objid, date(mtime) FROM event;
SELECT count(*) FROM t1;
.timer on
SELECT count(*) FROM t1
 WHERE date BETWEEN '2014-01-01' AND '2014-01-05';
WITH RECURSIVE
  range(date) AS (SELECT '2014-01-01'
                  UNION ALL
                  SELECT date(date,'+1 day') FROM range
                   WHERE date<'2014-01-05')
SELECT count(*) FROM t1
 WHERE date in range;

The t1 table above has 61856 dates in it, only 45 of which are in the
selected range.  Using BETWEEN is about twice the speed of using IN.

That makes sense.  The IN operator (usually) builds a transient table with
all of the values of the right-hand side, then does a membership test for
row of the input table.  The building of the transient table is a one-time
setup action so we can ignore the performance impact of that.  But then it
has to do a binary search of the transient table for each of the 61856 rows
of the input table.  A binary search of the 5-entry "range" table involves
about 3 comparisons, on average, whereas the BETWEEN operator involves
about 1.5 comparisons per row, on average (assuming the first comparison is
false about half the time).

Note that the to queries are not exactly equivalent though.  With the IN in
the WHERE clause, a date of '2014-01-03-extra-text' would be excluded
whereas it would be included using the BETWEEN operator.  So one way to
look at it is that the IN operator is being more selective and hence
requires additional comparisons.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: The IN (... ) clause

jose isaias cabrera

"Richard Hipp" wrote...

> On Sun, Sep 14, 2014 at 4:31 PM, Simon Slavin <[hidden email]>
> wrote:
>
>>
>> On 14 Sep 2014, at 8:58pm, jose isaias cabrera <[hidden email]>
>> wrote:
>>
>> > Yeah, that is what I am using now.  I was trying to get the speed that
>> supposedly is in the IN clause. :-)  Thanks.
>>
>> BETWEEN is fast[er] than IN....f
>>
>
> Confirmed.  Just to verify this, I ran the following script which times
> the
> same query using both IN and BETWEEN:
>
> .open /home/drh/www/repos/tcl.fossil
> CREATE TEMP TABLE t1(id INTEGER PRIMARY KEY, date TEXT);
> INSERT INTO t1 SELECT objid, date(mtime) FROM event;
> SELECT count(*) FROM t1;
> .timer on
> SELECT count(*) FROM t1
> WHERE date BETWEEN '2014-01-01' AND '2014-01-05';
> WITH RECURSIVE
>  range(date) AS (SELECT '2014-01-01'
>                  UNION ALL
>                  SELECT date(date,'+1 day') FROM range
>                   WHERE date<'2014-01-05')
> SELECT count(*) FROM t1
> WHERE date in range;
>
> The t1 table above has 61856 dates in it, only 45 of which are in the
> selected range.  Using BETWEEN is about twice the speed of using IN.
>
> That makes sense.  The IN operator (usually) builds a transient table with
> all of the values of the right-hand side, then does a membership test for
> row of the input table.  The building of the transient table is a one-time
> setup action so we can ignore the performance impact of that.  But then it
> has to do a binary search of the transient table for each of the 61856
> rows
> of the input table.  A binary search of the 5-entry "range" table involves
> about 3 comparisons, on average, whereas the BETWEEN operator involves
> about 1.5 comparisons per row, on average (assuming the first comparison
> is
> false about half the time).
>
> Note that the to queries are not exactly equivalent though.  With the IN
> in
> the WHERE clause, a date of '2014-01-03-extra-text' would be excluded
> whereas it would be included using the BETWEEN operator.  So one way to
> look at it is that the IN operator is being more selective and hence
> requires additional comparisons.

Thanks, Dr. Hipp.  This is perfect.  If I may, this should probably be part
of the documentation, as I am always looking for speed and this confirms the
result.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users