alias WHERE clause to simplify your query

Question

In SQL can you alias WHERE expressions so as to simplify your query?

Example I have

SELECT * FROM T
WHERE 
(x*y<15 AND x*z > 20)
OR 
(x*y>20 AND x*z < 100)

Is there a way to represent x*y as a and x*z as b so that I could rewrite my query as

SELECT * FROM T
WHERE 
(a<15 AND b>20)
OR 
(a>20 AND b<100)

I'm on SQL server

Similar question: http://stackoverflow.com/questions/260399/using-an-alias-column-in-the-where-clause-in-ms-sql-2000 — sazh, Mar 09 '12 at 02:21
Please do not prefix the titles with SQL. That is what tags are for — Justin Pihony, Mar 09 '12 at 03:17

Thit Lwin Oo · Accepted Answer · 2012-03-09T02:33:45.070

4

You can try this way.

SELECT *
FROM
(
    SELECT x, y, z, (x*y) xy, (x*z) xz
    FROM T
) R
WHERE (xy < 15 AND xz > 20) OR (xy > 20 AND xz < 100)

edited Mar 09 '12 at 02:33

answered Mar 09 '12 at 02:25

Thit Lwin Oo

3,388
3
20
23

score 2 · Answer 2 · answered Mar 09 '12 at 02:58

You can use a Common Table Expression if you are using SQL Server 2005 or later:

DECLARE @myTable TABLE([x] INT, [y] INT, [z] INT)
INSERT INTO @myTable VALUES(2, 1, 1)
INSERT INTO @myTable VALUES(3, 2, 2)
INSERT INTO @myTable VALUES(4, 2, 3)
INSERT INTO @myTable VALUES(5, 2, 5)
INSERT INTO @myTable VALUES(6, 4, 5)

;WITH CTE ([x], [y], [z], [xy], [xz]) AS
(
    SELECT x, y, z, x*y, x*z FROM @myTable
)
SELECT x, y, z 
FROM CTE
WHERE 
    (xy < 15 AND xz > 20)
    OR (xy > 20 AND xz < 100)

score 1 · Answer 3 · answered Mar 09 '12 at 14:09

1

WITH T1
     AS
     (
      SELECT T.*,
              x * y AS a, 
              x * z AS b
        FROM T
     )
SELECT * FROM T1
WHERE 
(a<15 AND b>20)
OR 
(a>20 AND b<100);

answered Mar 09 '12 at 14:09

onedaywhen

55,269
12
100
138

alias WHERE clause to simplify your query

3 Answers3