sql 2005中关键字"In"和"On"之间的区别 [英] difference Between keywords 'In' and 'On' in sql 2005

问题描述

任何人都可以打电话给我们，什么时候在SQL 2005中使用关键字IN和ON?
我们可以在SQL 2005中将IN关键字与索引一起使用吗??

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...?

推荐答案

SQL IN是用于提取与值列表匹配的数据的运算符.

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

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

ON与join一起使用.ON只能引用以前使用的表的字段.

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

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

ON关键字用于加入的情况.在join中，它返回所有满足该条件但记录不满足该条件的数据，但在特定字段中显示为空.

我们在Department表中有以下示例数据:

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:

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

这是Employees表的示例数据:

EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- ------------- --------
1约翰15000.00
2艾伯特1 4500.00
3 Crain 2 6000.00
4迈克尔25000.00
5 David NULL 34.00
6 Kelly空457.00
7鲁迪1 879.00
8史密斯2 7878.00
9卡森5 878.00
10纵梁5 345.00
11 Cheryl NULL NULL


假设我们被要求显示所有员工的名单，以及仅显示具有"HR"或"HR&会计部门;然后我们将编写如下查询:

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:

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

该查询的结果是:

EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- ------------- -------- ----------- ---------------
1约翰15000.00 1 HR
2 Albert 1 4500.00 1 HR
3 Crain 2 6000.00空NULL
4 Micheal 25000.00 NULL NULL
5 David NULL 34.00 NULL NULL
6 Kelly空457.00空NULL
7鲁迪1 879.00 1 HR
8史密斯2 7878.00 NULL NULL
9卡森(Kasen)5 878.00 5 HR&帐户
10纵梁5 345.00 5 HR&帐户
11 Cheryl NULL NULL NULL NULL


如果要求我们仅显示部门名称为"HR"或"HR&帐户"?我们将这样编写查询:

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:

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

该查询的结果是:

收合|复制代码
EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- ------------- -------- ----------- ---------------
1约翰15000.00 1 HR
2 Albert 1 4500.00 1 HR
7鲁迪1 879.00 1 HR
9卡森(Kasen)5 878.00 5 HR&帐户
10纵梁5 345.00 5 HR&帐户


您可以通过在查询中使用
来编写查询
从urTableName中选择*，其中rollNo在其中(从urSecondTable顺序中，按rollNo选择前10个rollNo).

我认为它将解决您的问题.

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.

这篇关于sql 2005中关键字"In"和"On"之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！