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
Difference between = and is in sql server
Nothing equals null
.
Not even null
equals null
.
null
is not a value, it is more like a concept, or a mark, meaning unknown value.
As such, you need two operators for this, one for equality, and one for checking the concept of null
.
Once you start to think of null
as "unknown value" a lot of the other behavior also makes sense.
10 + null
? Add an unknown value to 10? Obviously you will have another unknown value as a result.
For more information, please check the documentation of the equality operator in T-SQL.
Additionally, see the documentation for SET ANSI_NULL.
Note that the documentation is in conflict about the behavior of x = null
between the equality operator (documentation says it will always be false
if x is non-null
) whereas SET ANSI_NULLS
documentation says that x = null
will behave equivalent to x is null
when the option is turned on.
What is the difference between the IN operator and = operator in SQL?
IN
will not generate an error if you have multiple results on the subquery. Allows to have more than one value in the result returned by the subquery.
=
will generate an error if you have more than one result on the subquery.
- SQLFiddle Demo (IN vs =)
Difference between and != operators in MSSQL Server
!=
is not ANSI compliant.
That's all.
Use <>
UPD. Oh, here
Difference between and != in SQL
None whatsoever, syntactically.
Both are inequality operators, <>
is the SQL-92 standard, however its interchangable on some platforms with !=
(E.g. SQL Server)
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 != and operators?
From the manual:
Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.
So no, there is no difference between the two.
What is the difference between = and := in MySQL?
Both of them are assignment operators but one thing I can find their differences is that =
can be used to perform boolean operation while :=
cannot.
valid: SUM(val = 0)
Invalid: SUM(val := 0)
FROM User-Defined Variables
One more thing, You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements.
mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
| 1 | 2 | 4 | 7 |
+------+------+------+--------------------+
Performance differences between equal (=) and IN with one literal value
There is no difference between those two statements, and the optimiser will transform the IN
to the =
when IN
has just one element in it.
Though when you have a question like this, just run both statements, run their execution plan and see the differences. Here - you won't find any.
After a big search online, I found a document on SQL to support this (I assume it applies to all DBMS):
If there is only one value inside the parenthesis, this commend [sic] is equivalent to,
WHERE "column_name" = 'value1
Here is the execution plan of both queries in Oracle (most DBMS will process this the same):
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number = '123456789'
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
And for IN()
:
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number in('123456789');
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
As you can see, both are identical. This is on an indexed column. Same goes for an unindexed column (just full table scan).
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 |
+---------+---------+---------+---------+
Related Topics
How to Access Latest Row for Each Individual Identifier
Firstname, Lastname in Sql, Too Complex
How to Limit The Amount of Results Returned in Sybase
Have Pl/Sql Outputs in Real Time
Sql Server - Is Using @@Rowcount Safe in Multithreaded Applications
Difference of Create Index by Using Include Column or Not Using
"Pivoting" a Table in SQL (I.E. Cross Tabulation/Crosstabulation)
Sql Access Query- Update Row If Exists, Insert If Does Not
How to Control Nullability in Select into for Literal-Based Columns
How to Find Tables Which Reference a Particular Row via a Foreign Key
Attaching an Mdf File Without Ldf File
Display Multiple Rows and Column Values into a Single Row, Multiple Column Values
Sql Server Creating a Temp Table for This Query
On Duplicate Key Update Feature in H2
How to Group by One Column and Retrieve a Row with The Minimum Value of Another Column in T/Sql
Db (Sql) Automated Stress/Load Tools
What Is Wrong with My Update Statement with a Join in Oracle