Use column alias in WHERE clause

Question

I have a function in my query that uses a column in my table and results in a unique number for that input.

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t

I would also like to join another table which already has that unique number.

SELECT o.[SameUniqueValueAsGeneratedByTheFunctioninTable1] AS Col2
FROM Table2 o

I am not sure if this is possible but I would like to reference the function result to equal the column in Table2 for a successful join: say,

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t
LEFT JOIN Table2 o
ON Col1 = o.[SameUniqueValueAsGeneratedByTheFunctioninTable1]

Thanks for any clarification, help.

[Logical Processing Order of the SELECT statement](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15#logical-processing-order-of-the-select-statement) from SQL Server, but it's following standard SQL there. `SELECT` is *way* later than `JOIN`/`ON`. — Damien_The_Unbeliever, Feb 27 '20 at 13:07
Have you considered rewriting your scalar function into an inline table valued function? It is way more flexible and almost always (as long as it is inline and not multi-statement) will be quite a bit faster. — Sean Lange, Feb 27 '20 at 15:11

score 1 · Accepted Answer · answered Feb 27 '20 at 13:07

Many databases support lateral joins. I am guessing that you are using SQL Server, which supports these using APPLY. This allows you to move the column definition to the FROM clause:

SELECT . . . 
FROM Table1 t CROSS APPLY
     (VALUES (FunctionResultsinUniqueNumber(t.[table1Column]))
     ) v(col1) JOIN
     Table2 t2
     ON v.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

Otherwise, you would use a subquery:

SELECT . . . 
FROM (SELECT t.*,
             FunctionResultsinUniqueNumber(t.[table1Column] as col1
      FROM t
     ) t JOIN
     Table2 t2
     ON t.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

your cross apply was a bit faster than the other solutions here but I followed @SeanLange comment on my post to be helpful and just reevaluated my function to an extent, thanks for the help ! — pty, Mar 02 '20 at 16:48

score 1 · Answer 2 · answered Feb 27 '20 at 13:10

1

You need to call it again with ONclause :

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t LEFT JOIN 
     Table2 o
     ON FunctionResultsinUniqueNumber(t.[table1Column]) =
        o.[SameUniqueValueAsGeneratedByTheFunctioninTable1];

You can also use sub-query :

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM (SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1
      FROM Table1 t
      ) t LEFT JOIN
      Table2 t2
      ON t.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

answered Feb 27 '20 at 13:10

Yogesh Sharma

49,870
5
26
52

Note that query 1 will only work if the function FunctionResultsinUniqueNumber is deterministic. (Which it probably is...) – jarlh Feb 27 '20 at 13:53
Thanks, I did try both of these solutions; however, the query execution takes a long time (more for the former than the latter solution) – pty Feb 27 '20 at 14:02

Use column alias in WHERE clause

2 Answers2