Can repetitive WHERE clauses within a UNION query be consolidated?-CodePudding

I have a number of distinct tables in SQL that share the same schema, but contain different data. I was writing a query that utilizes a consistent WHERE clause on each of the tables and then UNIONs them all together. After writing a few of these I realize that copying the same WHERE clause again and again started to look very redundant, and I'm wondering if there's some way I can rewrite this query that doesn't repeat things so much. Here's a pared down version of the query I'm using:

SELECT a.primary_key, a.field1, b.field1 AS field2, 'One' AS source 
FROM table_1 a JOIN table_1_meta b ON a.primary_key = b.primary_key 
WHERE a.address_line_1 = '1234 Fake St' AND a.city = 'Beverly Hills' AND a.zip = '90210' 
UNION ALL 
SELECT a.primary_key, a.field1, b.field1 AS field2, 'Two' AS source 
FROM table_2 a JOIN table_2_meta b ON a.primary_key = b.primary_key 
WHERE a.address_line_1 = '1234 Fake St' AND a.city = 'Beverly Hills' AND a.zip = '90210' 
UNION ALL
SELECT a.primary_key, a.field1, b.field1 AS field2, 'Three' AS source 
FROM table_3 a JOIN table_3_meta b ON a.primary_key = b.primary_key 
WHERE a.address_line_1 = '1234 Fake St' AND a.city = 'Beverly Hills' AND a.zip = '90210' 
UNION ALL
SELECT a.primary_key, a.field1, b.field1 AS field2, 'Four' AS source 
FROM table_4 a JOIN table_4_meta b ON a.primary_key = b.primary_key 
WHERE a.address_line_1 = '1234 Fake St' AND a.city = 'Beverly Hills' AND a.zip = '90210'

Please refrain from any kinds of answers suggesting that I restructure the data/tables themselves, and suffice it to say that that is not an option in this situation. I am interested in learning whether this specific query can be refactored to consolidate the identical WHERE clauses.

CodePudding user response：

As mentioned in a comment above, you can union several queries without WHERE clauses, and then treat that as a derived table to which you apply the WHERE clause a single time.

Here how it would look:

SELECT t.primary_key, t.field1, t.field2, t.source FROM (
  SELECT a.primary_key, a.field1, b.field1 AS field2, 'One' AS source, a.address_line_1, a.city, a.zip
  FROM table_1 a JOIN table_1_meta b ON a.primary_key = b.primary_key 
  UNION ALL 
  SELECT a.primary_key, a.field1, b.field1 AS field2, 'Two' AS source, a.address_line_1, a.city, a.zip
  FROM table_2 a JOIN table_2_meta b ON a.primary_key = b.primary_key 
  UNION ALL
  SELECT a.primary_key, a.field1, b.field1 AS field2, 'Three' AS source, a.address_line_1, a.city, a.zip
  FROM table_3 a JOIN table_3_meta b ON a.primary_key = b.primary_key 
  UNION ALL
  SELECT a.primary_key, a.field1, b.field1 AS field2, 'Four' AS source, a.address_line_1, a.city, a.zip
  FROM table_4 a JOIN table_4_meta b ON a.primary_key = b.primary_key 
) AS t
WHERE t.address_line_1 = '1234 Fake St' AND t.city = 'Beverly Hills' AND t.zip = '90210'

You may notice that each of the unioned queries must expose the columns address_line_1, city, zip so that they are available in the derived table. Otherwise the WHERE conditions in the outer query would not be able to reference them. They must be columns of the derived table. But you don't have to include those extra columns in the select-list of the outer query.

So ultimately it doesn't look like a lot of savings in typing.

One possible downside of this is that the derived table might have millions and millions of rows and builds an enormous temporary table to hold them, only to have them mostly filtered out by the conditions in the WHERE clause of the outer query.

There is no guarantee that your SQL engine is clever enough to propagate the condition to each of the unioned queries that comprise the derived table. This may be implemented in some implementations of SQL that you use.

But you only tagged your question with sql. That tag is used on Stack Overflow for the SQL language generally, not any specific brand or product that implements SQL.