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 的大作中提到】
: 如果只是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 的大作中提到】
: 如果只是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来看看如果优化。 |
