m******u 发帖数: 12400 | 1 schema has 4 tables:
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Question:
Find the names of all employees in the database who live in the same cities
as the companies for which they work.
Answer:
select e.employee-name
from employee e, works w, company c
where e.employee-name = w.employee-name and e.city = c.city
and w.company-name = c.company-name
I thought we just need to join employee table and company table on their
city column.
My question is what wrong with my thought?
Thanks for your time and participation. |
c*****d 发帖数: 6045 | 2 如果只是employee和company连接
你的查询会返回所有相同city的employee和company
哪怕employee不为该company工作 |
x****e 发帖数: 1773 | 3 赞。
建议楼主建几个表,每个输入个几行数据,跑跑看看。个人觉得,这种方法对加深对常
用操作的理解非常有效。
【在 c*****d 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 如果只是employee和company连接 : 你的查询会返回所有相同city的employee和company : 哪怕employee不为该company工作
|
m******u 发帖数: 12400 | |
m******u 发帖数: 12400 | 5 schema has 4 tables:
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Question:
Find the names of all employees in the database who live in the same cities
as the companies for which they work.
Answer:
select e.employee-name
from employee e, works w, company c
where e.employee-name = w.employee-name and e.city = c.city
and w.company-name = c.company-name
I thought we just need to join employee table and company table on their
city column.
My question is what wrong with my thought?
Thanks for your time and participation. |
c*****d 发帖数: 6045 | 6 如果只是employee和company连接
你的查询会返回所有相同city的employee和company
哪怕employee不为该company工作 |
x****e 发帖数: 1773 | 7 赞。
建议楼主建几个表,每个输入个几行数据,跑跑看看。个人觉得,这种方法对加深对常
用操作的理解非常有效。
【在 c*****d 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 如果只是employee和company连接 : 你的查询会返回所有相同city的employee和company : 哪怕employee不为该company工作
|
m******u 发帖数: 12400 | |
s********t 发帖数: 8 | 9 select e.employee-name
from employee e inner join works w on e.employee-name = w.employee-name
inner join company c on w.company-name = c.company-name
where e.city = c.city
这样写可以吗?两者区别是什么呢? |
s********t 发帖数: 8 | 10 select e.employee-name
from employee e inner join works w on e.employee-name = w.employee-name
inner join company c on w.company-name = c.company-name
where e.city = c.city
这样写可以吗?两者区别是什么呢? |
s********t 发帖数: 8 | 11 select e.employee-name
from employee e inner join works w on e.employee-name = w.employee-name
inner join company c on w.company-name = c.company-name
where e.city = c.city
这样写可以吗?两者区别是什么呢? |
w****o 发帖数: 36 | |
q*********8 发帖数: 188 | 13 以上的几个query结果应该相同。 Performance可以会有不同, 建议使用SQL Server自
带的Execution plan来看看如果优化。 |