Menu
I'm wondering whether I can use EXISTS (or something similar) in column like this:
SELECT Column1,
Column2,
EXISTS (SELECT 1 FROM Table2 T2 WHERE T2.Column = T1.Column) AS IsFlag
FROM Table1
I know I can do something similar with Count()
SELECT Column1,
Column2,
(SELECT Count(*) FROM Table2 T2 WHERE T2.Column = T1.Column) AS IsFlag
FROM Table1
But that might not be very efficient when Table2 is large
238
If you have a small list of static values (and the values are not present in some table), the IN operator is preferred. If you need to check for existence of values in another table, the EXISTS operator is preferred as it clearly demonstrates the intent of the query.
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
If EXISTS (subquery) returns at least 1 row, the result is TRUE. If EXISTS (subquery) returns no rows, the result is FALSE. If NOT EXISTS (subquery) returns at least 1 row, the result is FALSE. If NOT EXISTS (subquery) returns no rows, the result is TRUE.
Checking Existence of the Column: For checking the existence we need to use the COL_LENGTH() function. COL_LENGTH() function returns the defined length of a column in bytes. This function can be used with the IF ELSE condition to check if the column exists or not.
Try this
SELECT Column1,
Column2,
CASE WHEN EXISTS (SELECT 1 FROM Table2 T2
WHERE T2.Column = T1.Column) then 1 ELSE 0 END AS IsFlag
FROM Table1
CASE
WHEN
EXISTS (SELECT 1 FROM Table2 T2 WHERE T2.Column = T1.Column)
THEN 1
ELSE 0
END AS IsFlag
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
