CROSS APPLY VS OUTER APPLY

https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

表A CROSS APPLY 表值函数 == INNER JOIN (表A 进行笛卡尔inner连接 表值函数返回n rows)

–OUTER APPLY
连接后的行数总会大于等于A表的行数
表A OUTER APPLY 表值函数 ==LEFT JOIN (表A 直接笛卡尔左连接 表值函数返回0/n rows )

CREATE TABLE Author
(
    id INT PRIMARY KEY,
    author_name VARCHAR(50) NOT NULL,
    
 )
 
CREATE TABLE Book
(
    id INT PRIMARY KEY,
    book_name VARCHAR(50) NOT NULL,
    price INT NOT NULL,
    author_id INT NOT NULL
   
 )

 
 INSERT INTO Author 
 
VALUES
(1, 'Author1'),
(2, 'Author2'),
(3, 'Author3'),
(4, 'Author4'),
(5, 'Author5'),
(6, 'Author6'),
(7, 'Author7')
 
 
INSERT INTO Book 
 
VALUES
(1, 'Book1',500, 1),
(2, 'Book2', 300 ,2),
(3, 'Book3',700, 1),
(4, 'Book4',400, 3),
(5, 'Book5',650, 5),
(6, 'Book6',400, 3)

查看效果

SELECT *
FROM Author

SELECT *
FROM Book

创建表值函数（接收作者id）

CREATE FUNCTION fnGetBooksByAuthorId(@AuthorId int)
RETURNS TABLE
AS
RETURN
( 
	SELECT * FROM Book
	WHERE author_id = @AuthorId
)



#返回作者id = 3 的书籍
SELECT * FROM fnGetBooksByAuthorId(3)	

cross apply

SELECT A.id, A.author_name, B.id, B.book_name, B.price
FROM Author A
--- 类似于inner join
CROSS APPLY fnGetBooksByAuthorId(A.id) B

outer apply

SELECT A.id,A.author_name, B.id, B.book_name, B.price
FROM Author A
-- 类似于 left join
OUTER APPLY fnGetBooksByAuthorId(-1) B

SELECT A.id,A.author_name, B.id, B.book_name, B.price
	FROM Author A
	-- 类似于 left join
	OUTER APPLY fnGetBooksByAuthorId(A.id) B

SELECT *
FROM fnGetBooksByAuthorId(3)