java sql左连接,SQL左外连接与n：m连接表

我想你需要 cartesian 结果 . 考虑以下示例，

declare @baskets table(basket_id int not null primary key identity, basketName varchar(255));

declare @fruits table(fruit_id int not null primary key identity, fruitName varchar(255));

declare @basketsFruits table(basket_id int not null, fruit_id int not null);

insert into @baskets(basketName)

values('basket x'), ('basket y');

insert into @fruits(fruitName)

values('apple'), ('strawberries'), ('grapes'), ('lemon');

insert into @basketsFruits(basket_id, fruit_id)

values(1, 1), (1, 2), (2, 1), (2, 2), (2, 3);

select b.*, f.fruitName

, case when exists(select 1 from @basketsFruits as bf where bf.basket_id = b.basket_id and bf.fruit_id = f.fruit_id) then

'Fruit Present'

else

'Fruit not Present'

end as fruitStatus

from @baskets as b, @fruits as f -- cartesian all the fruits and all the baskets

where b.basket_id = 1

Results: