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

Question

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

Answer 1

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
Echo

---

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.

Answer 2

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.