Query to display specific columns using a Set operator-CodePudding

I am trying to use a Set operator to show country names(Table A col.) without a city(Table B col.) and cities(B) without a country(A). I have also tried to write this query using LEFT JOINS, which I show below and I included Table C(Regions) because I am not sure whether to use that primary key in a LEFT JOIN.

Table A (Countries):

Column_name   |   Column_id
              |
COUNTRY_ID    |     1
COUNTRY_NAME  |     2
REGION_ID     |     3

Table B (Locations):

Column_name   |   Column_id
              |
LOCATION_ID   |     1
   CITY       |     4
COUNTRY_ID    |     6

Table C(Regions):

Column_name   |   Column_id
              |
 REGION_ID    |     1

I have tried the following:

SELECT c.country_name, l.city
FROM Countries c
LEFT JOIN Locations l ON c.country_id = l.country_id

UNION

SELECT c2.country_name, l2.city
FROM Countries c2
LEFT JOIN Locations l2 ON c2.country_id = l2.country_id;

The SQL statement above returned all Table A values, and Table A values that do not contain Table B values (Countries that do not have Cities).

I also tried this statement below and got the exact same result:

SELECT c.country_name, l.city
FROM Countries c
LEFT JOIN Locations l ON c.country_id = l.country_id
LEFT JOIN Regions r ON r.region_id = c.region_id;

The one thing it is missing is Table A values not found in Table B (Countries not found in Cities.)

CodePudding user response：

There are a lot of options to get your desired result. One way is to use LEFT JOIN to get the countries without city and RIGHT JOIN to get the cities without country:

SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM countries c
RIGHT JOIN locations l ON c.country_id = l.country_id
WHERE c.country_name IS NULL;

Another possibility is to use two LEFT JOIN, but starting from the opposite table, like this:

SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM locations l
LEFT JOIN countries c ON c.country_id = l.country_id
WHERE c.country_name IS NULL;

If you don't like using JOIN at all, you can do this using NOT IN:

SELECT c.country_name, NULL city
FROM countries c
WHERE country_id NOT IN (SELECT country_id FROM locations)
UNION ALL
SELECT NULL country_name, l.city
FROM locations l
WHERE country_id NOT IN (SELECT country_id FROM countries);

Or if you prefer NOT EXISTS, this will work, too:

SELECT c.country_name, NULL city
FROM countries c
WHERE NOT EXISTS (SELECT 1 FROM locations WHERE country_id = c.country_id)
UNION ALL
SELECT NULL country_name, l.city
FROM locations l
WHERE NOT EXISTS (SELECT 1 FROM countries WHERE country_id = l.country_id);

I created an example that shows all these queries will produce the identic outcome: db<>fiddle

Add ORDER BY c.country_name and ORDER BY l.city to the queries in case you want the result set to be sorted by them.

A last, but important note: As you see, I used UNION ALL instead of UNION because I don't see a reason why to use UNION in your use case. UNION ALL is much faster, so I recommend to use that unless there is a really convincing reason to do not use it. The only advantage of UNION is that it does not show duplicate rows, but I think they are very unlikely in your situation, so it should not be required.

CodePudding user response：

The simplest illustration of using a set operator to find countries without cities would be:

select country_id from countries
minus
select country_id from locations

COUNTRY_ID
----------
         1
         4

As you need the country name, you just need to look it up:

select country_name from countries
minus
select c.country_name from locations l
       join countries c on c.country_id = l.country_id;

COUNTRY_NAME
-----------------
England
Italy

Cities without a country (or with an invalid country code) is simpler as a left join and filter:

select l.city, l.country_id
from   locations l
       left join countries c on c.country_id = l.country_id
where  c.country_id is null

CITY             
-----------------
Berlin           
Tokyo

If the requirement really is to do this using set operators, you would (conceptually) look for cities whose country_id is in the set of (location countries minus city countries):

select l.location_id, l.city, l.country_id
from   locations l
where  l.country_id in
       ( select country_id from locations
         minus
         select country_id from countries )

However this wouldn't give you locations whose country_id was null.