Select from a subset of data

Question

I am working on a query in MS SQL Server 2014

That basically is a lot of unions

select x, y
where a = b
union
select x, y
where a = b
union
select x, y
where a = b

It works fine, however the where clauses are identical on every select. And for ease of maintenance I was wondering if there is a better, cleaner way to do this.

I was thinking of somehow selecting the data with the where clauses first then doing all the other queries only on this data.

But im open to any ideas on how to improve this query.

While off-topic, I was wondering if you actually want to remove duplicates from the union'ed result sets. If not, consider using `UNION ALL` for improved performance. — DocZerø, Apr 30 '15 at 14:13

score 4 · Accepted Answer · answered Apr 30 '15 at 14:07

4

;WITH Test AS
(
    SELECT x, y
    UNION
    SELECT x, y
    UNION  
    SELECT x, y
)
SELECT * FROM Test
WHERE a = b

answered Apr 30 '15 at 14:07

corky_bantam

329
4
11

In case the original poster hasn't encountered a CTE before, I think it's worth pointing out here that the weird semicolon-at-the-beginning construction is due to the requirement that if a CTE is not the first statement in a batch, the preceding statement must end with a semicolon. So putting one at the beginning of a line like this isn't necessary if you accustom yourself to terminating your statements with semicolons, which is probably [a good idea](http://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server). – Joe Farrell Apr 30 '15 at 14:14

score 1 · Answer 2 · answered Apr 30 '15 at 14:09

You could use a sub query and take the where clause outside of it for ease of maintenance. Just make sure you bring all the columns through in the sub query that you will need in the where clause. Eg

SELECT * FROM 
    (
        SELECT x,y,a,b FROM table1
        union
        SELECT x,y,a,b FROM table2
        UNION
        SELECT x,y,a,b FROM table3
    )subquery
    WHERE a=b

score 0 · Answer 3 · answered Apr 30 '15 at 14:05

0

Select * 
From
(
   select x, y

   union
   select x, y

   union
   select x, y
) MyDerivedTable
Where ...

Make sure to include the columns you need to filter in the select statement of the tables inside the derived table.

answered Apr 30 '15 at 14:05

Jeremy

4,808
2
21
24

1

While easier (no repetition of the where clause), you'd have to check the explain plan to see if the where clause is being pushed down to the individual tables, in order to avoid potential performance issues – DocZerø Apr 30 '15 at 14:07

Select from a subset of data

3 Answers3