CODEWITHSUNDEEP
sqlsql-servert-sql
SkyeBoniwell
SkyeBoniwell

Reputation: 7102

creating an efficient query with a UNION

I'm trying to get a list of all citizens of a country and list them in a table with 3 rows. The last row shows 'YES' or 'NO' depending on whether or not the person was born in a certain city.

What I did was create two queries: The first query gets all the citizens who were born in the city, and the second query gets all the citizens not born in the city.

Then I just do a UNION.

THe query runs and I think the results look good.

But I was wondering, is this the best way to form a query like this?

Thanks

--all citizens who were born in CityID = 5 who is in the USA
SELECT DISTINCT(CountryCitizenID), Country, 'YES' As Resident
FROM DT_Country_CitizenS dtu
INNER JOIN FT_City fte ON dtu.CountryCitizenID = fte.CitizenID
WHERE cityID = 5
AND Country = 'USA'

UNION

--all Citizens who were not born in CityID = 5 who is in the USA
SELECT  DISTINCT(CountryCitizenID), Country, 'NO' As Resident  
FROM DT_Country_CitizenS dtu 
WHERE NOT EXISTS (
    SELECT CitizenID 
    FROM FT_City fte 
    WHERE  dtu.CountryCitizenID = fte.CitizenID
    AND   cityID = 5)
AND Country = 'USA'

Upvotes: 0

Views: 102

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You shouldn't need a UNION for this (and generally, when you have two queries that can't possibly intersect, you should use UNION ALL which avoids a unique sort (or similar operation)). Also, DISTINCT cannot apply to a single column, as your current query seems to imply. I find it unlikely that there are duplicates in the CountryCitizenID column, unless it is really poorly named, so I don't think DISTINCT is needed at all. Finally, what is the point of pulling the Country column at all, since by definition it will be the same in every row? I've changed it to a constant instead of pulling the actual value (this might prevent a lookup or give a better chance at using a skinnier index), but you shouldn't need that column in the output at all - the application should know that, since the WHERE clause filters to only rows where Country = 'USA', there is no other value possible.

SELECT CountryCitizenID, Country = 'USA', Resident = CASE WHEN EXISTS
(
    SELECT 1
    FROM dbo.FT_City fte 
    WHERE fte.CitizenID = dtu.CountryCitizenID
    AND   fte.cityID = 5
) THEN 'YES' ELSE 'NO' END
FROM dbo.DT_Country_CitizenS AS dtu
WHERE dtu.Country = 'USA';

Upvotes: 5

sam yi
sam yi

Reputation: 4934

SELECT CountryCitizenID, Country, CASE WHEN fte.cityID IS NULL THEN 'No' ELSE 'Yes' END  As Resident
FROM DT_Country_CitizenS dtu
LEFT JOIN FT_City fte 
  ON dtu.CountryCitizenID = fte.CitizenID
  AND fte.cityID = 5
WHERE Country = 'USA'

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

Try this:

SELECT DISTINCT(CountryCitizenID), 
       Country, 
       case when fte.CitizenID is not null then 'YES' else 'NO' As Resident
  FROM DT_Country_CitizenS dtu
    LEFT JOIN (select * 
                 from FT_City 
                where cityID = 5) fte 
         ON (dtu.CountryCitizenID = fte.CitizenID)
 WHERE Country = 'USA'

Upvotes: 2

Related Questions