What is the difference between WHERE AND IN?
OR
1. SELECT * FROM EMPLOYEE WHERE EMPID=123
2. SELECT * FROM EMPLOYEE WHERE EMPID IN (123)
WHAT IS THE DIFFERENCE?
Answers were Sorted based on User's Feedback
Answer / jaipal singh
The IN operator allows you to specify multiple values in a
WHERE clause.
if we want to select the persons with a EMPID equal to 1,2
and 3 from the table above.
and simply where clause allow you to select ony whose EmpID
is 123.it is main difference
Is This Answer Correct ? | 21 Yes | 3 No |
Actually Both Query will return Same Result....
But Performance wise 1st Query better..Bcz it will return the Exact Record with out check hole record in the table....
But when we use IN operator then the sql engine will do the Table scan and retrive the Result...
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / abinash
Jaipal Sing: Lets say I will keep only one value (123) in IN
clause. In this case what is the real difference??
This is how Question asked to me!! and I am answer less...
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / chandan
IN operator is used to specify multiple variable.
while in this query both result will be same.
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / saiteja
In the case of where clause it displays single row,or operator can be placed in where clause in oreder to extend rows.
In IN operator at a time we can place empid multiple times but they must be unique.
ex:
(
select * from emp
where empid=123
);
By using OR operator:
(
select * from emp
where empid=123 or empid=234
);
By using IN operator:
(
select * from emp
where empid IN(123)
);
accordidg to the question the output will be same in both thae cases..
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / g.m. ershad
1)select * from test2 where id=44
Output -
ID NAME
44 Ershad
2) sELECT * FROM test2 WHERE ID IN (44)
Output -
ID NAME
44 Ershad
both will return same output
Is This Answer Correct ? | 6 Yes | 4 No |
Answer / soumya ghosh
Both of them will return the same record
But as per the first sentence of the question, WHERE is a Clause whereas IN is a Operator. That is the difference between both.
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / dinesh sharma
In WHERE Clause when the condition match no further scan of
table stop scanning the table after condition match.
where as IN operator It scan the full table either condition
match or not.
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / shashi kishor prasad
The where Clause allows you to give so many conditions like
in the above case you can use
select * from employee where empid >122
select * from employee where empid <125
select * from employee where empid between 122 and 125
select * from employee where empid is not null(if null allowed)
and many moer..........
where as
The in clause allows you to give a no of values inside the
parenthesis decided by you like
select * from employee where empid in(123,1234,125)
or
select * from employee where empid in(select empid from
employee where empid>200)
Is This Answer Correct ? | 1 Yes | 0 No |
what are the Prerequisites for Replication?
What is dbcc? Give few examples.
explain different types of jions with examples briefly?
List the ways in which dynamic sql can be executed?
How do I determine how many instances of sql server are installed on a computer?
Questions regarding Raiseerror?
How can you set the threshold at which sql server will generate keysets asynchronously?
What are the types of backup and tell me the difference between full and differential backup?
Which system tables contain information on privileges granted and privileges obtained
How to get a list of columns in a view using the "sp_columns" stored procedure?
What are the differences between triggers and stored procedures?
How will you monitor replication activity and performance? What privilege do you need to use replication monitor? : sql server replication