Clever Geek Handbook

SQL cuts duplicates in union article

let's say for example I have the following request:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

As you can see what is WHERE Country='Germany'

repeated in both the join goals - is there a way to reduce this to a non-repeating query? I have not liked my requests for too long.

I am currently working on Oracle.

+3


source to share


6 answers


Why not turn it on WHERE

just once, like

SELECT * FROM
(
SELECT City, Country FROM Customers
UNION ALL
SELECT City, Country FROM Suppliers
ORDER BY City
) tab 
WHERE Country='Germany'


(OR) execute JOIN

like

SELECT c.City as CustomerCity, c.Country as customerCountry, 
s.City as suppliercity, s.Country as suppliercountry
FROM Customers c
LEFT JOIN Suppliers s ON c.Country = s.Country
AND c.Country='Germany'
ORDER BY c.City;
+2


source 


select distinct city, country
from
(
   SELECT City, Country FROM Customers
   WHERE Country='Germany'
   UNION ALL
   SELECT City, Country FROM Suppliers
   WHERE Country='Germany'
) x
order by city


You really can't get around the need for UNION

if you really want both rowsets: I added UNION ALL

inside the main SQL and DISTINCT

for removing duplicates, but no additional sorting operations (if you want to do that).

+2


source 


SELECT * FROM
(
    SELECT City, Country FROM Customers
    UNION
    SELECT City, Country FROM Suppliers
) t
WHERE t.Country='Germany'
ORDER BY t.City;
+2


source


The code you already have is pretty compact and straightforward without sacrificing performance.

Suggestions for using a subquery can eliminate duplicate WHERE clauses, but will require more I / O than the simple union you originally provided. When there is a join inside the sub-query and then WHERE outside of it, it asks the SQL engine to create a temporary table that is all the rows in the customers table appended to all the rows in the supplier table, and then queries that summary table by discarding the rows that not a country = Germany. If your tables only have a few hundred rows and you are running your query locally, this probably won't show much of a performance difference, but if you have thousands of rows or tables on different servers across the network, performance can be orders of magnitude lower.

If performance is a concern, you can make the query a little easier and more convenient by using a country variable:

VAR country varchar2(64);
EXEC :country := 'Germany';

SELECT City, Country FROM Customers
WHERE Country=' :country
UNION
SELECT City, Country FROM Suppliers
WHERE Country= :country
ORDER BY City;

This clearly doesn't do it for shorter code, but it's somewhat cleaner and it would be easier to modify and only fetch the lines of interest, which will give better performance.

+2


source


You can use Common Table Expression (CTE):

WITH CTE AS (
    SELECT City, Country FROM Customers
    UNION
    SELECT City, Country FROM Suppliers)
SELECT  City, Country  
FROM CTE
WHERE CTE.Country='Germany'
ORDER BY CTE.City;

I find it easier to read than nested subqueries.

+1


source


Agree with Patrick. Query performance is much more important than query length. All mentioned alternatives have slower performance than your original one.

0


source






More articles:


All Articles