JOIN statement on an alias - SQL Server

Question

I am looking for advice on the best way to join using an alias instead of the original data. e.g the data is modified before it is joined.

An example:

CREATE TABLE Table1 (
No1 varchar(10)
);
CREATE TABLE Table2 (
No1 varchar(10)
);

INSERT INTO Table1 (No1)
VALUES ('222');
INSERT INTO Table2 (No1)
VALUES ('111');

If i created a join with a case statement but i wanted to join on the alias of the case statement this doesnt work with usual join syntax e.g.

SELECT
CASE WHEN T1.[No1] = '222' THEN '111' ELSE T1.[No1] END AS [T1 No], 
T2.[No1] AS [T2 No]
FROM Table1 T1
FULL JOIN Table2 T2
ON T1.[No1] = T2.[No1]

This gives result:

|  T1 No |  T2 No |
|--------+--------|
|    111 | (null) |
| (null) |    111 |

http://www.sqlfiddle.com/#!18/203e8/1

However, the approach i have taken to join on the alias is:

SELECT  
   T1.[T1 No],
   T2.[No1] AS [T2 No]
FROM
(
    SELECT
       CASE WHEN T1.[No1] = '222' THEN '111' ELSE T1.[No1] END AS [T1 No]
    FROM Table1 T1
) T1
JOIN Table2 T2
ON T1.[T1 No] = T2.[No1]

This gives result:

| T1 No | T2 No |
|-------+-------|
|   111 |   111 |

http://www.sqlfiddle.com/#!18/5fd7c/14

Which is exactly what i am looking for. However, the real life query i am dealing with is huge and sub-querying it to join on an alias makes it so messy.

Can anyone give me advice on a better approach to this? or is this the only way to do it?

Answer 1

Ok, firstly, it's probably good for you to be aware of the Logical Processing Order of the SELECT statement. Specifically, that order is:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Notice that that SELECT is the 8th thing to be processed, which is when the alias of a column would be processed. This means you can't reference a columns alias until step 9 (DISTINCT), which really means your left with doing so in the ORDER BY and that's it.

Thus, if you want to reference a column that is derived by an expression, you have few ways of doing so, some I have listed below.

1st way:

Use the Expression in the SELECT and the ON clause. Thus:

SELECT CASE WHEN T1.[No1] = '222' THEN '111'
                                  ELSE T1.[No1]
       END AS [T1 No], 
       T2.[No1] AS [T2 No]
FROM Table1 T1
     JOIN Table2 T2 ON CASE WHEN T1.[No1] = '222' THEN '111'
                                                  ELSE T1.[No1]
                       END = T2.[No1];

This can make things a little confusing, as it can make the query "busy".

2nd way:

Use a Subselect:

SELECT [T1 No]
FROM (SELECT CASE WHEN T1.[No1] = '222' THEN '111'
                                        ELSE T1.[No1]
             END AS [T1 No], 
      FROM Table1 T1) AS Tsq1
     JOIN Table2 T2 ON Tsq1.[T1 No] = T2.[No1];

3rd way

This is basically the same as the last option, however, using a CTE

WITH T1 AS (
    SELECT CASE WHEN T1.[No1] = '222' THEN '111'
                                        ELSE T1.[No1]
           END AS [T1 No], 
    FROM Table1 T1)
SELECT [T1 No]
FROM T1
     JOIN Table2 T2 ON T1.[T1 No] = T2.[No1];

4th Way:

You also could create a VIEW, and then JOIN on that:

CREATE VIEW Table1_vw AS

    SELECT *,
           SELECT CASE WHEN T1.[No1] = '222' THEN '111'
                                       ELSE T1.[No1]
                  END AS [T1 No]
    FROM Table1 T1;
GO

SELECT T1.[T1 No]
FROM Table1_vw T1
     JOIN Table2 T2 ON T1.[T1 No] = T2.[No1];

These are just a few options, but hopefully that puts you on the right path for what works for your own needs.