Standard SQL Boolean Operator Is VS. Equals (=) Operator

Standard SQL boolean operator IS vs. equals (=) operator

That's a new one for me.

If I read that correctly the <boolean value expression> grammar defines three predicates solely for use with the boolean datatype IS TRUE, IS FALSE, IS UNKNOWN.

These differ from their equality counterparts in that they only evaluate to True or False. Never to Unknown. i.e. UNKNOWN = TRUE would evaluate to UNKNOWN but UNKNOWN IS TRUE evaluates to False.

The full truth tables for IS and = are below.

+---------+-------+-------+---------+
|   IS    | TRUE  | FALSE | UNKNOWN |
+---------+-------+-------+---------+
| TRUE    | TRUE  | FALSE | FALSE   |
| FALSE   | FALSE | TRUE  | FALSE   |
| UNKNOWN | FALSE | FALSE | TRUE    |
+---------+-------+-------+---------+

As opposed to

+---------+---------+---------+---------+
|    =    |  TRUE   |  FALSE  | UNKNOWN |
+---------+---------+---------+---------+
| TRUE    | TRUE    | FALSE   | UNKNOWN |
| FALSE   | FALSE   | TRUE    | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+

How do the SQL IS and = operators differ?

You want records from Foo where Bar = @param, or if @param is null, where Bar is null. Some of the proposed solutions will give you null records with nonnull @param, which does not sound like your requirement.

Select * from Foo where (@param is null and Bar is null) or (Bar = @param)

This doesn't say whether this is Oracle or SQL Server or another RDBMS, because they each implement slightly different helper functions. SQL's ISNULL(first, second) like NVL(first, second). I like SQL Server's COALESCE() for the general applicability.

The IS comparison is only for null comparisons.

If you are using SQL Server and if you really need a different 3VL logic truth table to solve your problem (that is, if you have a specific need for "NULL=NULL" to be "true" at some point in time, and also recognize that this is deprecated and barring your reasons, not a good idea in general), within your code block you can use the directive

SET ANSI_NULLS OFF

Here's the BOL on it:
http://msdn.microsoft.com/en-us/library/ms188048.aspx

SQL: What's the difference between '=' and 'is'?

WHERE myvalue = null 

will never be true. Since even if "myvalue" is undefined ("null"), the database can't determine that it equals null (since null by definition means "unknown" and two "unknowns" can't be equal to each other).

WHERE myvalue IS null 

is more straight-forward. This checks whether "myvalue" is undefined (is "null" in database lingo).

SQLite - Difference between IS and = (equals) in WHERE clause. (using JDBC PreparedStatement)

The "IS" keyword is strictly to be used with NULL value. (eg. IS NULL or IS NOT NULL).

If you are looking for a match you need to use =.

If you are concerned about handling null column values then you should wrap the columns of concern with the isnull() function.

SELECT Col1
   , Col2
   , isnull(Col3,'') AS Col3
FROM myTable
Where col1 = 'somevalue'

What is the difference between NOT and != operators in SQL?

NOT negates the following condition so it can be used with various operators. != is the non-standard alternative for the <> operator which means "not equal".

e.g.

NOT (a LIKE 'foo%')
NOT ( (a,b) OVERLAPS (x,y) )
NOT (a BETWEEN x AND y)
NOT (a IS NULL)

Except for the overlaps operator above could also be written as:

a NOT LIKE 'foo%'
a NOT BETWEEN x AND y
a IS NOT NULL

In some situations it might be easier to understand to negate a complete expression rather then rewriting it to mean the opposite.

---

NOT can however be used with <> - but that wouldn't make much sense though: NOT (a <> b) is the same as a = b. Similarly you could use NOT to negate the equality operator NOT (a = b) is the same as a <> b

Which equals operator (== vs ===) should be used in JavaScript comparisons?

The strict equality operator (===) behaves identically to the abstract equality operator (==) except no type conversion is done, and the types must be the same to be considered equal.

Reference: Javascript Tutorial: Comparison Operators

The == operator will compare for equality after doing any necessary type conversions. The === operator will not do the conversion, so if two values are not the same type === will simply return false. Both are equally quick.

To quote Douglas Crockford's excellent JavaScript: The Good Parts,

JavaScript has two sets of equality operators: === and !==, and their evil twins == and !=. The good ones work the way you would expect. If the two operands are of the same type and have the same value, then === produces true and !== produces false. The evil twins do the right thing when the operands are of the same type, but if they are of different types, they attempt to coerce the values. the rules by which they do that are complicated and unmemorable. These are some of the interesting cases:

'' == '0'           // false
0 == ''             // true
0 == '0'            // true

false == 'false'    // false
false == '0'        // true

false == undefined  // false
false == null       // false
null == undefined   // true

' \t\r\n ' == 0     // true

The lack of transitivity is alarming. My advice is to never use the evil twins. Instead, always use === and !==. All of the comparisons just shown produce false with the === operator.

---

Update:

A good point was brought up by @Casebash in the comments and in @Phillipe Laybaert's answer concerning objects. For objects, == and === act consistently with one another (except in a special case).

var a = [1,2,3];
var b = [1,2,3];

var c = { x: 1, y: 2 };
var d = { x: 1, y: 2 };

var e = "text";
var f = "te" + "xt";

a == b            // false
a === b           // false

c == d            // false
c === d           // false

e == f            // true
e === f           // true

The special case is when you compare a primitive with an object that evaluates to the same primitive, due to its toString or valueOf method. For example, consider the comparison of a string primitive with a string object created using the String constructor.

"abc" == new String("abc")    // true
"abc" === new String("abc")   // false

Here the == operator is checking the values of the two objects and returning true, but the === is seeing that they're not the same type and returning false. Which one is correct? That really depends on what you're trying to compare. My advice is to bypass the question entirely and just don't use the String constructor to create string objects from string literals.

Reference
http://www.ecma-international.org/ecma-262/5.1/#sec-11.9.3

Equals(=) vs. LIKE

Different Operators

LIKE and = are different operators. Most answers here focus on the wildcard support, which is not the only difference between these operators!

= is a comparison operator that operates on numbers and strings. When comparing strings, the comparison operator compares whole strings.

LIKE is a string operator that compares character by character.

To complicate matters, both operators use a collation which can have important effects on the result of the comparison.

Motivating Example

Let us first identify an example where these operators produce obviously different results. Allow me to quote from the MySQL manual:

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

Please note that this page of the MySQL manual is called String Comparison Functions, and = is not discussed, which implies that = is not strictly a string comparison function.

How Does = Work?

The SQL Standard § 8.2 describes how = compares strings:

The comparison of two character strings is determined as follows:

a) If the length in characters of X is not equal to the length
in characters of Y, then the shorter string is effectively
replaced, for the purposes of comparison, with a copy of
itself that has been extended to the length of the longer
string by concatenation on the right of one or more pad
characters, where the pad character is chosen based on CS. If
CS has the NO PAD attribute, then the pad character is an
implementation-dependent character different from any
character in the character set of X and Y that collates less
than any string under CS. Otherwise, the pad character is a
<space>.

b) The result of the comparison of X and Y is given by the
collating sequence CS.

c) Depending on the collating sequence, two strings may
compare as equal even if they are of different lengths or
contain different sequences of characters. When the operations
MAX, MIN, DISTINCT, references to a grouping column, and the
UNION, EXCEPT, and INTERSECT operators refer to character
strings, the specific value selected by these operations from
a set of such equal values is implementation-dependent.

(Emphasis added.)

What does this mean? It means that when comparing strings, the = operator is just a thin wrapper around the current collation. A collation is a library that has various rules for comparing strings. Here is an example of a binary collation from MySQL:

static int my_strnncoll_binary(const CHARSET_INFO *cs __attribute__((unused)),
                               const uchar *s, size_t slen,
                               const uchar *t, size_t tlen,
                               my_bool t_is_prefix)
{
  size_t len= MY_MIN(slen,tlen);
  int cmp= memcmp(s,t,len);
  return cmp ? cmp : (int)((t_is_prefix ? len : slen) - tlen);
}

This particular collation happens to compare byte-by-byte (which is why it's called "binary" — it doesn't give any special meaning to strings). Other collations may provide more advanced comparisons.

For example, here is a UTF-8 collation that supports case-insensitive comparisons. The code is too long to paste here, but go to that link and read the body of my_strnncollsp_utf8mb4(). This collation can process multiple bytes at a time and it can apply various transforms (such as case insensitive comparison). The = operator is completely abstracted from the vagaries of the collation.

How Does LIKE Work?

The SQL Standard § 8.5 describes how LIKE compares strings:

The <predicate>

M LIKE P

is true if there exists a partitioning of M into substrings
such that:

i) A substring of M is a sequence of 0 or more contiguous
<character representation>s of M and each <character
representation> of M is part of exactly one substring.

ii) If the i-th substring specifier of P is an arbitrary
character specifier, the i-th substring of M is any single
<character representation>.

iii) If the i-th substring specifier of P is an arbitrary string
specifier, then the i-th substring of M is any sequence of
0 or more <character representation>s.

iv) If the i-th substring specifier of P is neither an
arbitrary character specifier nor an arbitrary string specifier,
then the i-th substring of M is equal to that substring
specifier according to the collating sequence of
the <like predicate>, without the appending of <space>
characters to M, and has the same length as that substring
specifier.

v) The number of substrings of M is equal to the number of
substring specifiers of P.

(Emphasis added.)

This is pretty wordy, so let's break it down. Items ii and iii refer to the wildcards _ and %, respectively. If P does not contain any wildcards, then only item iv applies. This is the case of interest posed by the OP.

In this case, it compares each "substring" (individual characters) in M against each substring in P using the current collation.

Conclusions

The bottom line is that when comparing strings, = compares the entire string while LIKE compares one character at a time. Both comparisons use the current collation. This difference leads to different results in some cases, as evidenced in the first example in this post.

Which one should you use? Nobody can tell you that — you need to use the one that's correct for your use case. Don't prematurely optimize by switching comparison operators.

Spark SQL = operator

Checkout the Section "Supported Hive Feature on Spark SQL Programming guide link and you will find it in the list of Hive Operators supported by Spark.

Here is what it does:

• Returns same result with EQUAL(=) operator for non-null operands

however:

• it returns TRUE if both are NULL

• it returns FALSE if one of them is NULL

---

Related Topics