WEEK 3 Join

left join

right join

Full join

cross join uchun misol

Join hammasi

Join davomi ikki tablda umumiy qismdan tashqi qisimlarni olish

Self join

join va subquery birgalikda ishlatilish shakli

Merge

https://www.w3resource.com/sql-exercises/sql-joins-exercises.php Joinlarni o'rganish uchun yaxshi sayt

Select nt.CategoryID,SUM(nt.price) price
From (Select pp.CategoryID,pp.ProductID,(
Select
SUM(od.UnitPrice*od.Quantity) price
From dbo.Products p
INNER JOIN dbo.[Order Details] od
on od.ProductID=p.ProductID
where pp.ProductID=p.ProductID) price
From dbo.Products pp
) as nt
Group by nt.CategoryID

select Color, s.size
from Colors
cross join
size s

#Cross join misol

create table TargetEmployee(
EmployeeID int ,
EmployeeName nvarchar(200),
Salary int,
Ispermanent int
)
insert into TargetEmployee([EmployeeID],[EmployeeName],[Salary],[Ispermanent])
values (1,'David',200,0),
(2,'tim',200,0),
(3,'marry',200,0),
(4,'Kevin',200,0)

create table Targetsourse(
EmployeeID int ,
EmployeeName nvarchar(200),
Salary int,
Ispermanent int
)

insert into Targetsourse([EmployeeID],[EmployeeName],[Salary],[Ispermanent])
values (1,'David',500,0),
(2,'tim',500,0),
(5,'alice',500,0),
(6,'cook',200,0)

select * from TargetEmployee

select * from Targetsourse

merge TargetEmployee as target
using Targetsourse as source
on source.EmployeeID=target.EmployeeID

when not matched by target then
insert (EmployeeID,EmployeeName,Salary, Ispermanent)
values(source.EmployeeID,source.EmployeeName,source.Salary,source.Ispermanent)

when matched then update set
target.Salary=source.Salary,
target.Ispermanent=source.Ispermanent

when not matched by source then
delete;