构建 SQL Join 以补充数据.需要 T-SQL 帮助 [英] Building SQL Join for complement of data. T-SQL help needed

问题描述

假设我有数据，

declare @TableA table
(
  TableAID int,
  TableAName varchar(10)
)

declare @TableB table
(
  TableBID int,
  TableBName varchar(10),
  TableAID int
)

insert into @TableA values
(1, 'A 1'),
(2, 'A 2'),
(3, 'A 3')

insert into @TableB values
(1, 'B 1', 1),
(2, 'B 2', 2)

我想编写一个连接而不是 SQL 查询，它会返回如下所示的数据，

I want to write a join and NOT SQL query which returns me data just as shown below,

TableAName TableBName
---------- ----------
A 3        N/A

简而言之，使用内部联接获得对视图的补充！

In short get a complement of the view with Inner Joins!

推荐答案

这是 OUTER JOIN 的经典用法，最常见的是使用 LEFT OUTER JOIN>(通常缩写为 LEFT JOIN)

This is a classic use for an OUTER JOIN and most commonly this is done using a LEFT OUTER JOIN (commonly abbreviated to just LEFT JOIN)

SELECT A.TableAName, B.TableBName
FROM TableA A
LEFT JOIN TableB B on A.TableAID = B.TableAID
WHERE B.TableAID IS NULL

外连接允许不相等的记录数，这里表A有3条，而表B有2条.当表B中没有匹配的数据时，NULL将存在，因此你可以过滤NULL，如上所示.

An outer join allows unequal record numbers, here TableA has 3 but TableB has 2. When there is no matching data in TableB NULLs will exist, and hence you can filter for NULL as shown above.

请帮自己一个忙，去这里进行可视化表示连接数&寻找左排除JOIN

Please do yourself a favour, go here for a visual representation of joins & look for Left Excluding JOIN

这篇关于构建 SQL Join 以补充数据.需要 T-SQL 帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！