如何在SQL Server中使用CAST连接两个表

I have two tables and I want to create a view to join them.

我有两个表，我想创建一个视图来加入它们。

I created a picture to explain my problem. As you can see in picture above, values in table 1 are unique and I want to know that if a value in table 1 exists in table 2 or not. I would like to add a column that contain "NO" If it doesn't exist, if it does additional column must contain "YES".

我创建了一张图片来解释我的问题。如上图所示，表1中的值是唯一的，我想知道表1中是否存在表1中的值。我想添加一个包含“NO”的列如果它不存在，如果它确实附加列必须包含“YES”。

I hope I could explain myself.

我希望我能解释一下自己。

4 个解决方案

#1

I would do this as:

我会这样做：

select t1.*,
       (case when exists (select 1 from table2 t2 where t2.col = t1.col)
             then 'YES'
             else 'NO'
        end) as flag
from table1 t1;

This should be the most efficient way to accomplish this goal. For best performance you want an index on table2(col).

这应该是实现这一目标的最有效方式。为获得最佳性能，您需要table2（col）上的索引。

#2

You can left join the table2 with table1 and see if any rows exists.

您可以将table2与table1连接起来，看看是否存在任何行。

select t1.col, case when count(t2.col) = 0 then 'No' else 'Yes' end 
from table1 t1
left join table2 t2
on t1.col = t2.col
group t1.col;

#3

;with A as
(select v from (values ('a'),('b'),('c'),('d'),('e')) v(v))
, B as
(select v from (values ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('d')) v(v))
-- This is where the magic happens
select
distinct a.v,case when b.v is null then 'NO' else 'YES' end existsinb
from  A
left join B
on a.v=b.v
order by v

#4

You can check both sides with full join:

您可以使用完整联接检查双方：

create view dbo.MyViewOfMissingValues
as
select 
    isnull(t1.col, t2.col) col, 
    case 
        when t2.col is null then 'No (missing in t2)' 
        when t1.col is null then 'No (missing in t1)' 
        else 'Yes' -- contained in both tables
    end col_status
from table1 t1
full join table2 t2
    on t1.col = t2.col
group isnull(t1.col, t2.col);