尽量实现单表查询，而避免使用 join,特别是在join的表的记录数非常多的情况下，是我主观认为的一个最佳实践
以下举例认为 Books和Authors两个表各有500万条记录，要求一次性查询出 Books的所有记录，并查询出 Books.AuthorID 关连的 Author.AuthorName
Books.AuthorID 是 Authors.AuthorID 的外键
以下是三种方法
方法一,个人认为性能最差
select b.*,a.* from Books b inner join Authors a on b.AuthorID=a.AuthorID
方法二,先过滤一部分 Author 的子集，然后在表变量join,个人认为性能比方法一好
create proc usp_Books_GetBooks
as
begin
DECLARE @t_Books table
(
BookId int,
BookName nvarchar(50),
UnitPrice bigint,
AuthorID int
);
insert into @t_Books select b.BookID,b.BookName,b.UnitPrice,b.AuthorID from Books b;
DECLARE @t_Authors table
(
AuthorID int,
AuthorName nvarchar(50)
);
insert into @t_Authors select a.AuthorID,a.AuthorName from Authors a where a.AuthorID in(select t_Books.AuthorID from @t_Books t_Books);
select t_Books.*,t_Authors.* from @t_Books t_Books inner join @t_Authors t_Authors on t_Authors.AuthorID=t_Books.AuthorID;
end
方法三,不做join,生成两个结果集，让开发人员在编程语言中实现(只考虑性能，不告诉开发人员是否方便)
create proc usp_Books_GetBooks_1
as
begin
DECLARE @t_Books table
(
BookId int,
BookName nvarchar(50),
UnitPrice bigint,
AuthorID int
);
insert into @t_Books select b.BookID,b.BookName,b.UnitPrice,b.AuthorID from Books b;
select a.AuthorID,a.AuthorName from Authors a where a.AuthorID in(select t_Books.AuthorID from @t_Books t_Books);
select * from @t_Books;
end
大家对方法二，三有什么看法，认为哪种比较好? 多谢
