difference Between keywords 'In' and 'On' in sql 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,
Could anyone plz tel when do we use keyword IN and ON in SQL 2005?
Can we use IN keyword with Indexes in SQL 2005...?

Posted 22-May-12 1:27am

Member 8660975

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

uspatel · Answer 1 · 2012-05-22T01:42:00

Solution 1

SQL IN is an operator used to pull data matching a list of values.for Ex

SQL

SELECT *
FROM orders
WHERE customer IN ('ABC','XYZ');

and ON is used with join.ON can only refer to the fields of previously used tables.as

SQL

SELECT *
FROM orders
JOIN inventory
ON orders.product = inventory.product;

Posted 22-May-12 1:42am

uspatel

Comments

Member 8660975 22-May-12 7:50am

thank u so much for the reply
can u plz tel can we use indexes in a query where 'in' keyword is used..?

uspatel 22-May-12 7:54am

Where you want to use IN in index?

Member 8660975 22-May-12 7:53am

Actually the matter is i need to fetch records of students based on the primary key that is thier Roll no. so we use select top 10 records from tablename where Rollno in(1,2,3,4,5) something like this.

so with this query can we use indexes to fetch records fast..?

Prosan · Answer 2 · 2012-05-24T00:49:00

ON keyword is used in case of join. in join it returns all data which will meet that condition but which record not meet that condition than it show null in particular field.

We have this sample data in the Department table:

VB

DeptId      DeptName
----------- ---------------
1           HR
2           Payroll
3           Admin
4           Marketing
5           HR & Accounts

And here is the sample data for the Employees table:

EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- ---------------------
1 John 1 5000.00
2 Albert 1 4500.00
3 Crain 2 6000.00
4 Micheal 2 5000.00
5 David NULL 34.00
6 Kelly NULL 457.00
7 Rudy 1 879.00
8 Smith 2 7878.00
9 Karsen 5 878.00
10 Stringer 5 345.00
11 Cheryl NULL NULL

Let’s say we are asked to show the list of all employees and the department name of only those employees who have “HR” or “HR & Accounts” department; then we would write a query like this:

SQL

select * from employees e
left join departments d on e.deptid = d.deptid
     and ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts')

and the result of this query is:

EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- --------------------- ----------- ---------------
1 John 1 5000.00 1 HR
2 Albert 1 4500.00 1 HR
3 Crain 2 6000.00 NULL NULL
4 Micheal 2 5000.00 NULL NULL
5 David NULL 34.00 NULL NULL
6 Kelly NULL 457.00 NULL NULL
7 Rudy 1 879.00 1 HR
8 Smith 2 7878.00 NULL NULL
9 Karsen 5 878.00 5 HR & Accounts
10 Stringer 5 345.00 5 HR & Accounts
11 Cheryl NULL NULL NULL NULL

if we are asked to show only those employees who have their department name “HR” or “HR & Accounts”? We would write a query like this:

SQL

select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname in ('HR','HR & Accounts'))

and the result of this query is:

Collapse | Copy Code
EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- --------------------- ----------- ---------------
1 John 1 5000.00 1 HR
2 Albert 1 4500.00 1 HR
7 Rudy 1 879.00 1 HR
9 Karsen 5 878.00 5 HR & Accounts
10 Stringer 5 345.00 5 HR & Accounts

you can write your query by using in query

select * from urTableName where rollNo in (select top 10 rollNo from urSecondTable order by rollNo).

i think it will solve your problem.