一道SQL面试题目

Question

昨天去面试，面试官给了我一道sql查询题，说不要看着简单，至今还没有人做对，要考虑效率，题目如下：
表T_SEL1
id    int    not  null  Pk
t2id int    not  null  FK  （关联T_SEL2的pk id）
表T_SEL2
id            int        not null pk
name  varchar(4)要求：得到表T_SEL1、表T_SEL2的连接
查询条件是：T_SEL2.id<100 and T_SEL2.name='xyz'我写的答案是：
select a.*,b.* from t_sel1 a,t_sel2 b
where a.t2id=b.id
and b.id<=100
and b.name = 'xyz'想问问大家有没有更高效的方法？ 

Answer 1

为了方便大家测试，我把建表脚本发上来，数据库是sql server2000
脚本如下：
--创建表结构
IF OBJECT_ID('T_SEL2') IS NOT NULL
  DROP TABLE T_SEL2
GO
CREATE TABLE T_SEL2(id int not null PRIMARY KEY,name varchar(4))
GO
IF OBJECT_ID('T_SEL1') IS NOT NULL
  DROP TABLE T_SEL1
GO
CREATE TABLE T_SEL1(id int not null,t2id int not null)
GO
ALTER TABLE T_SEL1 ADD
  CONSTRAINT [FK_T_SEL1_T_SEL2] FOREIGN KEY (t2id) REFERENCES T_SEL2 (id)
--创建测试数据
delete from t_sel1;
delete from t_sel2;
--插入对象
declare @index int;
set @index =1;
while (@index<10000)
BEGIN
insert into t_sel2 values(@index,'xyz');
insert into t_sel1 values(@index,@index);
set @index = @index +1;
End 

Answer 2

楼主注意了 把你的* 全部换成列。这样会快很多
在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典
完成的, 这意味着将耗费更多的时间
IF OBJECT_ID('T_SEL2') IS NOT NULL 
  DROP TABLE T_SEL2 
GO 
CREATE TABLE T_SEL2(id int not null PRIMARY KEY,name varchar(4)) 
GO 
IF OBJECT_ID('T_SEL1') IS NOT NULL 
  DROP TABLE T_SEL1 
GO 
CREATE TABLE T_SEL1(id int not null,t2id int not null) 
GO 
ALTER TABLE T_SEL1 ADD 
  CONSTRAINT [FK_T_SEL1_T_SEL2] FOREIGN KEY (t2id) REFERENCES T_SEL2 (id) 
--创建测试数据 
delete from t_sel1; 
delete from t_sel2; 
--插入对象 
declare @index int; 
set @index =1; 
while (@index <10000) 
BEGIN 
insert into t_sel2 values(@index,'xyz'); 
insert into t_sel1 values(@index,@index); 
set @index = @index +1; 
End select a.id,
a.t2id,
b.id,
b.name
 from t_sel1 a,(select  * from t_sel2 where id <=100 and name = 'xyz') b 
where a.t2id=b.id 
/*
id          t2id        id          name
----------- ----------- ----------- ----
1           1           1           xyz
2           2           2           xyz
3           3           3           xyz
4           4           4           xyz
5           5           5           xyz
6           6           6           xyz
7           7           7           xyz
8           8           8           xyz
9           9           9           xyz
10          10          10          xyz
11          11          11          xyz
12          12          12          xyz
13          13          13          xyz
14          14          14          xyz
15          15          15          xyz
16          16          16          xyz
17          17          17          xyz
18          18          18          xyz
19          19          19          xyz
20          20          20          xyz
21          21          21          xyz
22          22          22          xyz
23          23          23          xyz
24          24          24          xyz
25          25          25          xyz
26          26          26          xyz
27          27          27          xyz
28          28          28          xyz
29          29          29          xyz
30          30          30          xyz
31          31          31          xyz
32          32          32          xyz
33          33          33          xyz
34          34          34          xyz
35          35          35          xyz
36          36          36          xyz
37          37          37          xyz
38          38          38          xyz
39          39          39          xyz
40          40          40          xyz
41          41          41          xyz
42          42          42          xyz
43          43          43          xyz
44          44          44          xyz
45          45          45          xyz
46          46          46          xyz
47          47          47          xyz
48          48          48          xyz
49          49          49          xyz
50          50          50          xyz
51          51          51          xyz
52          52          52          xyz
53          53          53          xyz
54          54          54          xyz
55          55          55          xyz
56          56          56          xyz
57          57          57          xyz
58          58          58          xyz
59          59          59          xyz
60          60          60          xyz
61          61          61          xyz
62          62          62          xyz
63          63          63          xyz
64          64          64          xyz
65          65          65          xyz
66          66          66          xyz
67          67          67          xyz
68          68          68          xyz
69          69          69          xyz
70          70          70          xyz
71          71          71          xyz
72          72          72          xyz
73          73          73          xyz
74          74          74          xyz
75          75          75          xyz
76          76          76          xyz
77          77          77          xyz
78          78          78          xyz
79          79          79          xyz
80          80          80          xyz
81          81          81          xyz
82          82          82          xyz
83          83          83          xyz
84          84          84          xyz
85          85          85          xyz
86          86          86          xyz
87          87          87          xyz
88          88          88          xyz
89          89          89          xyz
90          90          90          xyz
91          91          91          xyz
92          92          92          xyz
93          93          93          xyz
94          94          94          xyz
95          95          95          xyz
96          96          96          xyz
97          97          97          xyz
98          98          98          xyz
99          99          99          xyz
100         100         100         xyz(100 行受影响)
*/ select a.id,
a.t2id,
b.id,
b.name from t_sel1 a,t_sel2 b 
where a.t2id=b.id 
and b.id <=100 
and b.name = 'xyz' 

Answer 3

--TRY
select 
   a.*,b.* 
from 
   t_sel1 a 
join  on 
   t_sel2 b 
where 
   a.t2id=b.id 
and 
   b.id <=100 
and 
   b.name = 'xyz' 

Answer 4

--修改
select 
   a.*,b.* 
from 
   t_sel1 a 
join 
   t_sel2 b 
on 
  a.t2id=b.id 
where
   b.id <=100 
and 
   b.name = 'xyz'

Answer 5

select * from T_sel1 t1,T_SEL2 t2
where t1.t2id = t2.id and t2.id<=100 and t2.name = 'xyz'/**
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 3 毫秒。
id          t2id        id          name 
----------- ----------- ----------- ---- 
1           1           1           xyz
2           2           2           xyz
3           3           3           xyz
4           4           4           xyz
5           5           5           xyz
6           6           6           xyz
7           7           7           xyz
8           8           8           xyz
9           9           9           xyz
10          10          10          xyz
11          11          11          xyz
12          12          12          xyz
13          13          13          xyz
14          14          14          xyz
15          15          15          xyz
16          16          16          xyz
17          17          17          xyz
18          18          18          xyz
19          19          19          xyz
20          20          20          xyz
21          21          21          xyz
22          22          22          xyz
23          23          23          xyz
24          24          24          xyz
25          25          25          xyz
26          26          26          xyz
27          27          27          xyz
28          28          28          xyz
29          29          29          xyz
30          30          30          xyz
31          31          31          xyz
32          32          32          xyz
33          33          33          xyz
34          34          34          xyz
35          35          35          xyz
36          36          36          xyz
37          37          37          xyz
38          38          38          xyz
39          39          39          xyz
40          40          40          xyz
41          41          41          xyz
42          42          42          xyz
43          43          43          xyz
44          44          44          xyz
45          45          45          xyz
46          46          46          xyz
47          47          47          xyz
48          48          48          xyz
49          49          49          xyz
50          50          50          xyz
51          51          51          xyz
52          52          52          xyz
53          53          53          xyz
54          54          54          xyz
55          55          55          xyz
56          56          56          xyz
57          57          57          xyz
58          58          58          xyz
59          59          59          xyz
60          60          60          xyz
61          61          61          xyz
62          62          62          xyz
63          63          63          xyz
64          64          64          xyz
65          65          65          xyz
66          66          66          xyz
67          67          67          xyz
68          68          68          xyz
69          69          69          xyz
70          70          70          xyz
71          71          71          xyz
72          72          72          xyz
73          73          73          xyz
74          74          74          xyz
75          75          75          xyz
76          76          76          xyz
77          77          77          xyz
78          78          78          xyz
79          79          79          xyz
80          80          80          xyz
81          81          81          xyz
82          82          82          xyz
83          83          83          xyz
84          84          84          xyz
85          85          85          xyz
86          86          86          xyz
87          87          87          xyz
88          88          88          xyz
89          89          89          xyz
90          90          90          xyz
91          91          91          xyz
92          92          92          xyz
93          93          93          xyz
94          94          94          xyz
95          95          95          xyz
96          96          96          xyz
97          97          97          xyz
98          98          98          xyz
99          99          99          xyz
100         100         100         xyz（所影响的行数为 100 行）
**/

Answer 6

子查询会慢一些select a.id,
a.t2id,
b.id,
b.name
 from t_sel1 a,(select  * from t_sel2 where id <=100 and name = 'xyz') b 
where a.t2id=b.id /**
SQL Server 分析和编译时间: 
   CPU 时间 = 4 毫秒，耗费时间 = 4 毫秒。
id          t2id        id          name 
----------- ----------- ----------- ---- 
1           1           1           xyz
2           2           2           xyz
3           3           3           xyz
4           4           4           xyz
5           5           5           xyz
6           6           6           xyz
7           7           7           xyz
8           8           8           xyz
9           9           9           xyz
10          10          10          xyz
11          11          11          xyz
12          12          12          xyz
13          13          13          xyz
14          14          14          xyz
15          15          15          xyz
16          16          16          xyz
17          17          17          xyz
18          18          18          xyz
19          19          19          xyz
20          20          20          xyz
21          21          21          xyz
22          22          22          xyz
23          23          23          xyz
24          24          24          xyz
25          25          25          xyz
26          26          26          xyz
27          27          27          xyz
28          28          28          xyz
29          29          29          xyz
30          30          30          xyz
31          31          31          xyz
32          32          32          xyz
33          33          33          xyz
34          34          34          xyz
35          35          35          xyz
36          36          36          xyz
37          37          37          xyz
38          38          38          xyz
39          39          39          xyz
40          40          40          xyz
41          41          41          xyz
42          42          42          xyz
43          43          43          xyz
44          44          44          xyz
45          45          45          xyz
46          46          46          xyz
47          47          47          xyz
48          48          48          xyz
49          49          49          xyz
50          50          50          xyz
51          51          51          xyz
52          52          52          xyz
53          53          53          xyz
54          54          54          xyz
55          55          55          xyz
56          56          56          xyz
57          57          57          xyz
58          58          58          xyz
59          59          59          xyz
60          60          60          xyz
61          61          61          xyz
62          62          62          xyz
63          63          63          xyz
64          64          64          xyz
65          65          65          xyz
66          66          66          xyz
67          67          67          xyz
68          68          68          xyz
69          69          69          xyz
70          70          70          xyz
71          71          71          xyz
72          72          72          xyz
73          73          73          xyz
74          74          74          xyz
75          75          75          xyz
76          76          76          xyz
77          77          77          xyz
78          78          78          xyz
79          79          79          xyz
80          80          80          xyz
81          81          81          xyz
82          82          82          xyz
83          83          83          xyz
84          84          84          xyz
85          85          85          xyz
86          86          86          xyz
87          87          87          xyz
88          88          88          xyz
89          89          89          xyz
90          90          90          xyz
91          91          91          xyz
92          92          92          xyz
93          93          93          xyz
94          94          94          xyz
95          95          95          xyz
96          96          96          xyz
97          97          97          xyz
98          98          98          xyz
99          99          99          xyz
100         100         100         xyz（所影响的行数为 100 行）
SQL Server 执行时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 14 毫秒。
**/

Answer 7

select t1.id,t1.t2id,t2.id,t2.name from T_sel1 t1,T_SEL2 t2
where t1.t2id = t2.id and t2.id<=100 and t2.name = 'xyz'
/**
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 3 毫秒。
id          t2id        id          name 
----------- ----------- ----------- ---- 
1           1           1           xyz
2           2           2           xyz
3           3           3           xyz
4           4           4           xyz
5           5           5           xyz
6           6           6           xyz
7           7           7           xyz
8           8           8           xyz
9           9           9           xyz
10          10          10          xyz
11          11          11          xyz
12          12          12          xyz
13          13          13          xyz
14          14          14          xyz
15          15          15          xyz
16          16          16          xyz
17          17          17          xyz
18          18          18          xyz
19          19          19          xyz
20          20          20          xyz
21          21          21          xyz
22          22          22          xyz
23          23          23          xyz
24          24          24          xyz
25          25          25          xyz
26          26          26          xyz
27          27          27          xyz
28          28          28          xyz
29          29          29          xyz
30          30          30          xyz
31          31          31          xyz
32          32          32          xyz
33          33          33          xyz
34          34          34          xyz
35          35          35          xyz
36          36          36          xyz
37          37          37          xyz
38          38          38          xyz
39          39          39          xyz
40          40          40          xyz
41          41          41          xyz
42          42          42          xyz
43          43          43          xyz
44          44          44          xyz
45          45          45          xyz
46          46          46          xyz
47          47          47          xyz
48          48          48          xyz
49          49          49          xyz
50          50          50          xyz
51          51          51          xyz
52          52          52          xyz
53          53          53          xyz
54          54          54          xyz
55          55          55          xyz
56          56          56          xyz
57          57          57          xyz
58          58          58          xyz
59          59          59          xyz
60          60          60          xyz
61          61          61          xyz
62          62          62          xyz
63          63          63          xyz
64          64          64          xyz
65          65          65          xyz
66          66          66          xyz
67          67          67          xyz
68          68          68          xyz
69          69          69          xyz
70          70          70          xyz
71          71          71          xyz
72          72          72          xyz
73          73          73          xyz
74          74          74          xyz
75          75          75          xyz
76          76          76          xyz
77          77          77          xyz
78          78          78          xyz
79          79          79          xyz
80          80          80          xyz
81          81          81          xyz
82          82          82          xyz
83          83          83          xyz
84          84          84          xyz
85          85          85          xyz
86          86          86          xyz
87          87          87          xyz
88          88          88          xyz
89          89          89          xyz
90          90          90          xyz
91          91          91          xyz
92          92          92          xyz
93          93          93          xyz
94          94          94          xyz
95          95          95          xyz
96          96          96          xyz
97          97          97          xyz
98          98          98          xyz
99          99          99          xyz
100         100         100         xyz（所影响的行数为 100 行）
SQL Server 执行时间: 
   CPU 时间 = 13 毫秒，耗费时间 = 13 毫秒。
**/

Answer 8

先按照条件求出表二的记录 再和表一关联借鉴楼上
select a.id,
a.t2id,
b.id,
b.name
from t_sel1 a,(select  * from t_sel2 where id <=100 and name = 'xyz') b 
where a.t2id=b.id 

Answer 9

select a.id,
a.t2id,
b.id,
b.name
 from t_sel1 a,(select  * from t_sel2 where id <=100 and name = 'xyz') b 
where a.t2id=b.id 
应该是这个，给应届生的题。

Answer 10

select a.id, 
a.t2id, 
b.id, 
b.name 
from t_sel1 a,(select  * from t_sel2 where id <=100 and name = 'xyz') b 
where a.t2id=b.id 
我觉得这样效率好点

Answer 11

这种问题，还是不用join语法是最快的。
实际执行了一下，数据量为100W，刚开始的时候执行了两个查询，用了3秒。再以后都是0，各个查询再看不出差别了。应该是利用了缓存。
几种查询是 不用join，用连接子查询，用连接不用子查询，列名，和*
组合进行。
各位不防一试。

Answer 12

select a.[id],a.t2id,b.[name] from 
(select [id],t2id from t_sel1 where t2id<100) a join
(select [id],[name] from t_sel2 where [id]<100 and [name]='xyz') b
on a.t2id=b.[id]