How to add boolean column based on expression?-CodePudding

Table T1:

Id	R1	R2	R3
Id1	r1_d1	r2_d1	r3_d1
Id2	r1_d2	r2_d2	r3_d2
Id3	r1_d3	r2_d2	r3_d3
Id4	r1_d4	r2_d4	r3_d4

Table T2:

Id	K_R1	K2
Id1	r1_d1	k2_d1
Id2	r1_d3	k2_d2
Id3	r1_d4	k2_d4

I need some properties of first table and an additional result indicating if T1.R1 == T2.K_R1:

Id	R1	R2	P1
Id1	r1_d1	r2_d1	true
Id2	r1_d2	r2_d2	false
Id3	r1_d3	r2_d2	true
Id4	r1_d4	r2_d4	true

LEFT JOIN will return all entries of T1 and matching entries of T2, but will have all properties of T2 too, which is not needed. How (considering performance too) to exclude unnecessary properties and produce a boolean- instead of actual value?

CodePudding user response：

You may use EXISTS operator as the following:

SELECT Id, R1, R2,
       EXISTS(SELECT 1 FROM table2 T2 WHERE T2.K_R1 = T1.R1) AS P1
FROM table1 T1
ORDER BY Id, R1

See a demo.

CodePudding user response：

The join itself doesn't "return" anything, so I wouldn't worry about the number of columns that could be returned. You can put exactly what you want in your SELECT clause.

You can use a CASE statement to return true or false.

Maybe something like:

SELECT 
 t1.id, 
 t1.r1, 
 t1.r2, 
 CASE WHEN t2.id IS NULL THEN 'false' ELSE 'true' END p1
FROM t1 LEFT OUTER JOIN t2
 ON t1.r1 = t2.k_r1

SELECT 
 t1.*, 
 CASE WHEN t2.id IS NULL THEN 'false' ELSE 'true' END p1
FROM t1 LEFT OUTER JOIN t2
  ON t1.r1 = t2.k_r1

Those options assume that there is at most one matching record in t2. If that isn't the case, you would probably want to either use GROUP BY (with the JOIN) or use an EXISTS in your SELECT (without the JOIN). The MAX in this is example should work since "true" is alphabetically greater than "false", and if there is at least one "true" you want to return "true."

SELECT 
 t1.id, 
 t1.r1, 
 t1.r2, 
 MAX(CASE WHEN t2.id IS NULL THEN 'false' ELSE 'true' END) p1
FROM t1 LEFT OUTER JOIN t2
 ON t1.r1 = t2.k_r1
GROUP BY t1.id, t1.r1, t1.r2