Essential SQL Queries Every Developer Should Know

Despite the rise of NoSQL databases, relational databases (like PostgreSQL, MySQL, and SQL Server) remain the backbone of most enterprise software systems. Understanding how to efficiently extract, manipulate, and analyze relational data is a critical skill for any backend developer or data engineer. In this tutorial, we will move beyond basic SELECT * statements and explore essential intermediate SQL concepts.

1. Mastering JOINs

Relational databases store data in normalized tables to prevent duplication. To get a complete picture, you need to combine tables using JOINs.

INNER JOIN: Returns records that have matching values in both tables.
LEFT JOIN: Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.

-- Find all orders and their associated customer details
-- If a customer has no orders, they will NOT appear in this result
SELECT customers.name, orders.order_date, orders.total_amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- Find ALL customers, and their orders if they have any
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

2. Aggregations and GROUP BY

Often, you don't want raw rows; you want summarized data (e.g., total sales per user, average account balance). This is where aggregation functions (COUNT, SUM, AVG, MAX, MIN) and the GROUP BY clause come in.

-- Calculate the total revenue generated by each customer
SELECT customers.name, SUM(orders.total_amount) as lifetime_value
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY lifetime_value DESC;

3. The HAVING Clause

What if you only want to see customers whose lifetime value is greater than $1,000? You cannot use a WHERE clause on aggregated data. Instead, you must use the HAVING clause.

SELECT customers.name, SUM(orders.total_amount) as lifetime_value
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
HAVING SUM(orders.total_amount) > 1000;

4. Subqueries and Common Table Expressions (CTEs)

Sometimes, writing a query requires multiple logical steps. While you can nest queries (Subqueries), utilizing a Common Table Expression (CTE) using the WITH clause makes complex queries significantly more readable.

-- Find all customers who spent above the average order amount
WITH AverageOrder AS (
    SELECT AVG(total_amount) as avg_amount FROM orders
)
SELECT customers.name, orders.total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total_amount > (SELECT avg_amount FROM AverageOrder);

Writing efficient SQL is an art. Always remember to analyze your query execution plans (using EXPLAIN in PostgreSQL/MySQL) to ensure you are utilizing indexes effectively and avoiding full table scans on large datasets!

Written by A.M. Rinas