SQL Server – Return Value from WHERE EXISTS Without JOIN

sql serversql-server-2008

Trying to figure out how to write the following pseudo-code in valid SQL (MS SQL Server):

SELECT 
  T1.Item,
  T1.Set,
  Has.SN
FROM T1
 WHERE EXISTS
(SELECT SN FROM T2 WHERE Condition = 'X') AS Has

Essentially, if the SN exists on T2, then return the SN along with the unrelated values from T1. If the SN does not exist, no data should be returned (or could all return as NULL)

But Item and Set must be correlated from the same row on T1, and I need the SN returned as well. (I'm not sure that this would be guaranteed if using separate CASE statements.)

There's nothing relatable to JOIN T1 and T2 on.

(Note that this is just intended to be one sub-component of a query, I need it to return those 3 columns in a manner than can be used in other joins.)

I'm thinking there's a really simple solution to this, but I'm drawing a blank.

I've done some searching, but I'm not finding any examples that are quite like this.

UPDATE

So sample data would be like:

T1

+----+---------+-----+
| ID | ItemID  | Set |
+----+---------+-----+
| 1  | 2424424 | 4   |
+----+---------+-----+
| 2  | 2454677 | 7   |
+----+---------+-----+

T2

+----+-----------+-----------+
| ID | SN        | Condition |
+----+-----------+-----------+
| 1  | SN0284202 | X         |
+----+-----------+-----------+
| 2  | SN1902552 | NULL      |
+----+-----------+-----------+

I guess basically, I need a separate row returned for each Item and Set per SN that matches the condition. So if there's 1000 rows on T1, and 100 rows on T2, but only 10 of them match the condition, there should be 10,000 rows returned. (1000 items for each of the 10 SNs)
Obviously this could make for a large data set, but it's just being used to sub-query specific data.

Example Result

+---------+-----+-----------+
| ItemID  | Set | SN        |
+---------+-----+-----------+
| 2424424 | 4   | SN0284202 |
+---------+-----+-----------+
| 2454677 | 7   | SN0284202 |
+---------+-----+-----------+

Best Answer

This is likely what you're looking for:

SELECT 
    T1.Item,
    T1.[Set],
    Has.SN
FROM T1
CROSS JOIN
(
    SELECT 
        SN 
    FROM T2 
    WHERE Condition = 'X'
) AS Has;

You can't project columns from the EXISTS portion, because items in the select list there are not projected anywhere.

As an example, if you change your original query to something functional:

SELECT 
    T1.Item,
    T1.[Set]
FROM T1
WHERE EXISTS
(
    SELECT 
        1/0 
    FROM T2 
    WHERE Condition = 'X'
);

The 1/0 would normally produce a divide by zero, but doesn't in this case because it's not evaluated by the optimizer.

Related Question