Query to Display Specific Columns Using a Set Operator

The problem presented in the question revolves around querying specific columns from two tables using set operators. In this blog post, we will explore different approaches to achieve this goal.

Understanding the Tables

To tackle this problem, it’s essential to understand the structure of the tables involved:

Table A: Countries

Table B: Locations

Table C: Regions

Query Approaches

The question presents three query approaches to achieve the desired result:

1. Using LEFT JOIN and RIGHT JOIN
2. Using two LEFT JOINs
3. Using NOT IN or NOT EXISTS

Let’s delve into each approach with explanations, code snippets, and examples.

Approach 1: Using LEFT JOIN and RIGHT JOIN

One way to achieve the desired result is by using a combination of LEFT JOIN and RIGHT JOIN. This involves joining the Countries table with the Locations table on the COUNTRY_ID column and then filtering for rows where the join does not return any matches. Similarly, you can use another LEFT JOIN to achieve the same result from the opposite perspective.

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;

Approach 2: Using Two LEFT JOINs

Another approach involves using two LEFT JOINs. The first join is performed from the perspective of the Countries table, and the second join is performed from the opposite perspective.

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;

Approach 3: Using NOT IN or NOT EXISTS

A third approach to achieving the desired result involves using the NOT IN operator or the NOT EXISTS clause. The NOT IN operator is used in conjunction with a subquery that selects all unique values from the country_id column of the Locations table. Similarly, the NOT EXISTS clause can be used to achieve the same result.

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

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);

Example Use Case

To illustrate the output of these queries, we can create an example database with sample data. You can use an online SQL editor like db<>fiddle to execute the following query:

CREATE TABLE Countries (
    COUNTRY_ID INT PRIMARY KEY,
    COUNTRY_NAME VARCHAR(255),
    REGION_ID INT
);

CREATE TABLE Locations (
    LOCATION_ID INT PRIMARY KEY,
    CITY VARCHAR(255),
    COUNTRY_ID INT
);

CREATE TABLE Regions (
    REGION_ID INT PRIMARY KEY
);

INSERT INTO Countries (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
VALUES (1, 'United States', 1), (2, 'Canada', 2), (3, 'Mexico', 1);

INSERT INTO Locations (LOCATION_ID, CITY, COUNTRY_ID)
VALUES (1, 'New York', 1), (2, 'Toronto', 2), (3, 'Los Angeles', 1),
       (4, 'Mexico City', 3);

INSERT INTO Regions (REGION_ID)
VALUES (1), (2), (3);

After executing the query using any of the approaches mentioned above, you should see the expected output:

This demonstrates that the queries are capable of retrieving countries without cities and cities without corresponding countries.

Additional Advice

When using set operators like UNION ALL, keep in mind that they do not eliminate duplicate rows. If you want to avoid duplicates, use the UNION operator instead. However, the UNION operator has a slight performance overhead due to its additional processing steps.

In general, it’s essential to choose the most suitable query approach based on your specific requirements and database schema.

Conclusion

In this blog post, we explored three different approaches to query specific columns using set operators in SQL. We delved into each approach with code snippets and explanations, highlighting their strengths and weaknesses. By understanding these query methods, you can effectively tackle similar problems in your own projects and improve your overall SQL skills.

Last modified on 2025-01-14