Add Column on left join and set entry only if record exists in the second table

相关文章推荐

近视的苦瓜 · 工业硅主要的三大下游消费领域分别为多晶硅、有 ...· 4 月前 ·

爱搭讪的哑铃 · 来自精神病院的强者漫画 - 抖音· 1 年前 ·

活泼的打火机 · 世荣翠湖苑二期2室2厅1卫户型图-珠海乐居· 1 年前 ·

微醺的鸡蛋面 · 联想小新air - 商品搜索 - 京东· 1 年前 ·

豪气的双杠 · 贵阳铁路运输法院· 1 年前 ·

I have two tables A and B. I want to perform a left join on A . And want to add column to the result. The entries of that column should contain a value on intersetion of A and B else NULL. The following query performs what I'm looking for.

Is there a way to query the following result without the without the intermediate table "TableA"?

WITH TableA AS (
       SELECT *
             ,'hello' AS Col1
             , 1 AS Col2
       FROM A
)  SELECT B.*
            ,TableA.Col1
            ,TableA.Col2
      FROM B
      LEFT JOIN TableA ON TableA.Id = B.Id
												While asking a question you need to provide a minimal reproducible example:  

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.  

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.  

(3) Desired output based on the sample data in the #1 above.  

(4) Your SQL Server version (SELECT @@version;)
												Do you have any update?  

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.   
						Hi @Artimes  ,    
Yes, for the same problem, many times TSQL can have multiple methods.    
First I want to explain that TableA is not an intermediate table, it is not even a real table. It is a subquery in the form of a table, that is, a subquery is used instead of a table where it should be. This is called a table expression. The main function of table expressions is to construct various temporary data sets conveniently.    
TableA in your code is a common table expression, which is defined with with. After TableA is defined, it can be referenced in any code afterwards.    
If you do not want to use a common table expression, you can choose to use a derived table (another table expression) instead, please refer to:    
SELECT B.*,t.Col1,t.Col2  
FROM B  
LEFT JOIN (SELECT *,'hello' AS Col1, 1 AS Col2 FROM A) t  
ON t.Id = B.Id  
If you have any question, please feel free to let me know.    
Regards     

If the answer is helpful, please click "Accept Answer" and upvote it.    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.    
						The potential with that problem is that if an ID in B can match multiple rows in A, you will get multiple instances of those rows from B. Is this what you want? In such case, the above query is good. TableA is not table, it is a Common Table Expression which is a purely logical concept. There is no intermediate storage for it.  
If you don't want multiple rows, you can do:  
SELECT B.*, CASE WHEN EXISTS (SELECT * FROM A WHERE A.Id = B.Id) THEN 1 END AS Extracol
FROM   B
Although, if you want two columns, it is a bit of a bummer since you need to repeat the CASE.