34,836
社区成员




- create table Customers (Cust_Id int, Cust_Name varchar(10))
-
- insert Customers values (1, 'Craig')
-
- insert Customers values (2, 'John Doe')
-
- insert Customers values (3, 'Jane Doe')
-
- create table Sales (Cust_Id int, Item varchar(10))
-
- insert Sales values (2, 'Camera')
-
- insert Sales values (3, 'Computer')
-
- insert Sales values (3, 'Monitor')
-
- insert Sales values (4, 'Printer')
- select *
-
- from Sales S inner join Customers C
-
- on S.Cust_Id = C.Cust_Id
-
- Cust_Id Item Cust_Id Cust_Name
-
- ----------- ---------- ----------- ----------
-
- 2 Camera 2 John Doe
-
- 3 Computer 3 Jane Doe
-
- 3 Monitor 3 Jane Doe
- select *
-
- from Sales S left outer join Customers C
-
- on S.Cust_Id = C.Cust_Id
-
- Cust_Id Item Cust_Id Cust_Name
-
- ----------- ---------- ----------- ----------
-
- 2 Camera 2 John Doe
-
- 3 Computer 3 Jane Doe
-
- 3 Monitor 3 Jane Doe
-
- 4 Printer NULL NULL
- select *
-
- from Sales S full outer join Customers C
-
- on S.Cust_Id = C.Cust_Id
-
- Cust_Id Item Cust_Id Cust_Name
-
- ----------- ---------- ----------- ----------
-
- 2 Camera 2 John Doe
-
- 3 Computer 3 Jane Doe
-
- 3 Monitor 3 Jane Doe
-
- 4 Printer NULL NULL
-
- NULL NULL 1 Craig
- Join 保留…
- -------------------------------------------
- A left outer join B all A rows
- A right outer join B all B rows
- A full outer join B all A and B rows
- select *
-
- from Sales S cross join Customers C
-
- Cust_Id Item Cust_Id Cust_Name
-
- ----------- ---------- ----------- ----------
-
- 2 Camera 1 Craig
-
- 3 Computer 1 Craig
-
- 3 Monitor 1 Craig
-
- 4 Printer 1 Craig
-
- 2 Camera 2 John Doe
-
- 3 Computer 2 John Doe
-
- 3 Monitor 2 John Doe
-
- 4 Printer 2 John Doe
-
- 2 Camera 3 Jane Doe
-
- 3 Computer 3 Jane Doe
-
- 3 Monitor 3 Jane Doe
-
- 4 Printer 3 Jane Doe
- create function dbo.fn_Sales(@Cust_Id int)
-
- returns @Sales table (Item varchar(10))
-
- as
-
- begin
-
- insert @Sales select Item from Sales where Cust_Id = @Cust_Id
-
- return
-
- end
-
- select *
-
- from Customers cross apply dbo.fn_Sales(Cust_Id)
-
- Cust_Id Cust_Name Item
-
- ----------- ---------- ----------
-
- 2 John Doe Camera
-
- 3 Jane Doe Computer
-
- 3 Jane Doe Monitor
- select *
-
- from Customers outer apply dbo.fn_Sales(Cust_Id)
-
- Cust_Id Cust_Name Item
-
- ----------- ---------- ----------
-
- 1 Craig NULL
-
- 2 John Doe Camera
-
- 3 Jane Doe Computer
-
- 3 Jane Doe Monitor
- select *
-
- from Customers C
-
- where exists (
-
- select *
-
- from Sales S
-
- where S.Cust_Id = C.Cust_Id
-
- )
-
- Cust_Id Cust_Name
-
- ----------- ----------
-
- 2 John Doe
-
- 3 Jane Doe
-
- |--Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
- |--Table Scan(OBJECT:([Customers] AS [C]))
- |--Table Scan(OBJECT:([Sales] AS [S]))