In our previous guide, we covered the basics of retrieving data, performing joins, and managing records. However, real-world database management often requires more dynamic data manipulation. Mastering intermediate commonly used sql queries will allow you to build complex backend features and write optimized analytics queries.

In this second part, we will explore some of the most powerful commonly used sql queries, covering nested subqueries, pattern matching, conditional outputs, and combining result sets.

1. Nested Queries (Subqueries)

A subquery is a query nested inside another SQL statement. Subqueries are extremely useful when you need to perform calculations or fetch parameters dynamically to filter the main query. For example, if you want to find all employees who earn more than the company’s average salary:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the inner query (SELECT AVG(salary) FROM employees) executes first to calculate the average salary. The outer query then retrieves only the employees earning above that average figure.

2. Combining Result Sets (UNION vs. UNION ALL)

The UNION operator is used to combine the result sets of two or more SELECT statements into a single output. Each query within the union must have the same number of columns with compatible data types.

// Get a list of all client and supplier contact emails
SELECT email FROM clients
UNION
SELECT email FROM suppliers;

UNION vs. UNION ALL Comparison

3. Pattern Matching (LIKE & Wildcards)

When searching for text records without knowing the exact string, the LIKE operator combined with wildcards is the standard choice:

• % (Percent sign): Represents zero, one, or multiple characters.
• _ (Underscore): Represents exactly one single character.

// Find all customers with Gmail addresses
SELECT customer_name, email 
FROM customers 
WHERE email LIKE '%@gmail.com';

// Find employees whose names start with 'J' and are exactly 4 characters long (e.g. John, Jane)
SELECT first_name 
FROM employees 
WHERE first_name LIKE 'J___';

4. Conditional Logic in Queries (CASE WHEN)

The CASE expression is SQL’s version of the if-else statement. It allows you to evaluate conditions and return specific values directly in your query output. You can read more about conditional logic on the official PostgreSQL Documentation.

SELECT product_name, price,
       CASE 
           WHEN price > 100 THEN 'Expensive'
           WHEN price > 50 THEN 'Moderate'
           ELSE 'Inexpensive'
       END AS price_category
FROM products;

This query evaluates the price of each product and outputs a user-friendly text category alongside the raw data.

Summary

Mastering these intermediate patterns will allow you to build sophisticated search systems, handle data merging, and construct cleaner API payloads. To check the basic syntax of JOINs and aggregations, make sure to read Most Commonly Used SQL Queries (Part 1).