A Quick Guide to SQL — Chapter 7: Advanced SQL Concepts

3 min readJun 20, 2023

In the previous lesson we learned about modifying data. In this lesson, we will delve into advanced SQL concepts that can enhance your data retrieval and analysis capabilities. We will explore subqueries and nested queries, as well as important operators such as EXISTS, NOT EXISTS, IN, NOT IN, UNION, INTERSECT, and EXCEPT. By the end of this lesson, you will have a solid understanding of these advanced SQL concepts and how to leverage them effectively. Let’s get started!

Chapters of This Series

1. Subqueries and Nested Queries

Subqueries, also known as nested queries, allow you to nest one query inside another. This enables you to retrieve data from one query and use it as input for another query. Subqueries can be used in the SELECT, FROM, WHERE, and HAVING clauses. Here’s an example of using a subquery in the WHERE clause to find customers who have placed an order:

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

In this example, the subquery (SELECT customer_id FROM orders) retrieves all customer IDs from the "orders" table. The outer query then selects the names of customers whose IDs are present in the subquery's result.

2. EXISTS and NOT EXISTS Operators

The EXISTS and NOT EXISTS operators are used to check for the existence or non-existence of rows returned by a subquery. The EXISTS operator returns true if the subquery returns any rows, while the NOT EXISTS operator returns true if the subquery does not return any rows. Here’s an example of using the EXISTS operator to find customers who have placed at least one order:

SELECT name
FROM customers
WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id);

In this example, the subquery checks if there is any row in the “orders” table that matches the customer ID in the outer query. If a match is found, the EXISTS condition is satisfied, and the customer’s name is selected.

3. IN and NOT IN Operators

The IN and NOT IN operators are used to compare a value against a set of values returned by a subquery. The IN operator returns true if the value is found in the subquery’s result, while the NOT IN operator returns true if the value is not found. Here’s an example of using the IN operator to find customers from specific cities:

SELECT name
FROM customers
WHERE city IN ('New York', 'London', 'Paris');

In this example, the query selects customers whose city is either ‘New York’, ‘London’, or ‘Paris’.

4. UNION, INTERSECT, and EXCEPT Operators

The UNION, INTERSECT, and EXCEPT operators are used to combine or compare the results of two or more SELECT statements.

UNION combines the result sets of two or more SELECT statements into a single result set, eliminating duplicate rows. For example:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

INTERSECT returns the common rows between the result sets of two or more SELECT statements. For example:

SELECT name FROM customers
INTERSECT
SELECT name FROM suppliers;

EXCEPT returns the rows from the first SELECT statement that are not present in the result sets of subsequent SELECT statements. For example:

SELECT name FROM customers
EXCEPT
SELECT name FROM suppliers;

5. Conclusion

In this lesson, we explored advanced SQL concepts such as subqueries and nested queries, the EXISTS and NOT EXISTS operators, the IN and NOT IN operators, and the UNION, INTERSECT, and EXCEPT operators. These concepts allow you to perform complex data retrieval and analysis tasks, enabling you to extract valuable

If you found this course helpful and would like to explore more free courses, I invite you to follow my account on Medium and connect with me on LinkedIn. I regularly share valuable content on these platforms.