The IS operator

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

The IS operator

J. King-3
I've been using the IS operator to the exclusion of = in my
application's queries so that I'm not surprised by some input being null
and giving unexpected results. My rationale is that if I'm consistent,
my software will be as well (all else being equal, anyway...).

The IS operator is commonly seen in tutorials and the like as "x IS NOT
NULL", and Richard's example earlier today of the unary NOT operator "a
= NOT b" started me wondering if there are any performance or
portability concerns with using IS rather than =. Are there any other
reasons I might perhaps want to use = rather than IS that I'm not aware
of?

Thanks. :)

--
J. King

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

Re: The IS operator

Richard Hipp-3
On 10/6/17, J. King <[hidden email]> wrote:
> Are there any other
> reasons I might perhaps want to use = rather than IS that I'm not aware
> of?

"NULL IS NULL" is true, but "NULL = NULL" is not true (it is null).
Other than that, the IS operator and the = operator are the same.

"IS" in SQLite is the same as "IS NOT DISTINCT FROM" in PostgreSQL and
"IS NOT" in SQLite is the ame as "IS DISTINCT FROM" in PostgreSQL.

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

Re: The IS operator

J. King-3
Thank you, Richard. After some digging I see, too, that MySQL uses <=> for that same functionality, Microsoft has a toggle for =, and Oracle nothing. I had clearly been mistaken about IS's portability.

On October 6, 2017 7:53:27 PM EDT, Richard Hipp <[hidden email]> wrote:

>On 10/6/17, J. King <[hidden email]> wrote:
>> Are there any other
>> reasons I might perhaps want to use = rather than IS that I'm not
>aware
>> of?
>
>"NULL IS NULL" is true, but "NULL = NULL" is not true (it is null).
>Other than that, the IS operator and the = operator are the same.
>
>"IS" in SQLite is the same as "IS NOT DISTINCT FROM" in PostgreSQL and
>"IS NOT" in SQLite is the ame as "IS DISTINCT FROM" in PostgreSQL.
>
>--
>D. Richard Hipp
>[hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users