4

I am currently learning SQL utilizing Codecademy and am curious if there is a difference between using "IS" or "=".

In the current lesson, I wrote this code:

SELECT *
FROM nomnom
WHERE neighborhood IS 'Midtown'
  OR neighborhood IS 'Downtown'
  OR neighborhood IS 'Chinatown';

Which ran perfectly fine. I always like to look at the answer after to see if there was something I did wrong or could improve on. The answer had this code:

SELECT *
FROM nomnom
WHERE neighborhood = 'Midtown'
   OR neighborhood = 'Downtown'
   OR neighborhood = 'Chinatown';

Do IS and = function the same?

Paul Hobbs
  • 43
  • 5
  • 1
    Does this answer your question? [How do the SQL "IS" and "=" operators differ?](https://stackoverflow.com/questions/5423751/how-do-the-sql-is-and-operators-differ) – Mafor Dec 30 '19 at 22:51
  • Equals ´=´ is an operator. ´IS NULL´ is an operator and is used as part of an expression `name IS NOT NULL` There is no such thing as `IS` in T-SQL. – thomas77 Dec 30 '19 at 22:55
  • @Community This question is about SQLite, though the proposed duplicate is about other DBMS. In SQLite the operators IS and IS NOT behave differently. Check the documentation and the answers. – forpas Dec 30 '19 at 23:03

3 Answers3

4

All that you want to know you can find it here:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

taken from: SQL As Understood By SQLite.
The important part is: ...except when one or both of the operands are NULL... because when using = or != (<>) and 1 (or both) of the operands is NULL then the result is also NULL and this is the difference to IS and IS NOT.

forpas
  • 160,666
  • 10
  • 38
  • 76
1

They work the same but "IS" is a keyword in MySQL and is generally used while comparing NULL values. While comparing NULL values "=" does not work.

SELECT * FROM nomnom WHERE neighborhood IS NULL

The above statement would run perfectly fine but 

SELECT * FROM nomnom WHERE neighborhood = NULL

would result in an error.

risingStark
  • 1,153
  • 10
  • 17
  • Thank you for the response. When not working with NULL values, they do function the same though? Is "=" the preferred way to handle non NULL values? – Paul Hobbs Dec 30 '19 at 22:51
  • Its not about preference. Just that IS keyword has been kind of reserved for NULL values. But it looks more acceptable to use = instead of IS. In layman terms, you are right, long story short, = is more preferred. – risingStark Jun 01 '20 at 13:35
0

They are the same for these cases, but further down the line you will discover one nifty little value called NULL.

NULL is a pain because... it doesn't exist. 0 = NULL returns FALSE; Date <> [Column] will not return lines with NULL, only those with a value that is different. Hell, even NULL = NULL returns false. And NULL <> NULL also returns false. That is why "IS" exists. Because NULL IS NULL will return true.

So as a general rule, use = for values. Keep "IS" for null.

[Column] IS NULL

or 

[Column] IS NOT NULL

And remember to always check if your column is nullable that you need to plan for null values in your WHERE or ON clauses.

Pierre
  • 159
  • 4
  • `NULL = NULL` does *not* return "false". It evaluates to `NULL`, which `where` clauses filter out along with "false" values. – Gordon Linoff Dec 31 '19 at 00:13