From Tables To Trends: Understanding Joins And Window Functions In Sql

What is SQL used for?

SQL (Structured Query Language) is primarily used for managing and manipulating data in relational databases. It is the common language for communicating with databases such as Oracle, Microsoft SQL Server, PostgreSQL, and MySQL, allowing users to carry out a variety of operations from simple data management to intricate analysis.

JOINS

In SQL, a JOIN clause is used to combine rows from two or more tables based on a related column between them. The primary purpose of joins is to retrieve data that has been spread across multiple tables in a relational database, providing a comprehensive, unified view in a single result set. It helps answer business questions like which customer placed an order or which product generated revenue.

Window Functions

SQL window functions perform calculations across a set of table rows related to the current row, without merging those rows into a single output like a GROUP BY clause would. They return a result for each individual row, which is useful for tasks such as calculating running totals, rankings, and moving averages.

In real-world databases, data rarely lives in one table. Joins help us combine related data, while window functions allow us to analyze patterns without losing row-level detail.

Types of JOINS

1.INNER JOIN

It is used to combine rows from two or more tables based on a specified common column with matching values. It returns only the set of records that have a match in all the tables involved, effectively acting as an intersection of the data sets
Rows that do not have a corresponding match in the other table(s) are excluded from the result set.

Example

SELECT customers.name, orders.order_date  
FROM customers  
INNER JOIN orders  
    ON customers.customer_id = orders.customer_id;  

This query returns only customers who have placed orders because INNER JOIN keeps matching records from both tables.

2.LEFT JOIN

The SQL LEFT JOIN (or LEFT OUTER JOIN) is a fundamental operation that retrieves all rows from the left (first) table and matching rows from the right (second) table. If a row in the left table has no corresponding match in the right table based on the join condition, the result will contain NULL values for the columns of the right table.

Example

SELECT   
    customers.name,  
    orders.order_date  
FROM customers  
LEFT JOIN orders  
    ON customers.customer_id = orders.customer_id;  

This query keeps all customers, if a customer has an order → the order date appears. If a customer has never placed an order → order_date will be NULL.

3.RIGHT JOIN

The SQL RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right-hand table and only the matching records from the left-hand table. If a row in the right table has no match in the left table, the columns from the left table in the result set will contain NULL values.

Example

SELECT   
    customers.name,  
    orders.order_date  
FROM customers  
RIGHT JOIN orders  
    ON customers.customer_id = orders.customer_id;  

This query It keeps all orders, if an order has a matching customer → the name appears. If there’s no matching customer → customers.name will be NULL.

4.FULL OUTER JOIN

A FULL OUTER JOIN (or FULL JOIN) in SQL returns all rows from both the left and right tables, combining matching records and using NULL values for columns where no match is found.

Example

SELECT   
    customers.name,  
    orders.order_date  
FROM customers  
FULL OUTER JOIN orders  
    ON customers.customer_id = orders.customer_id;  

This query returns Customers with orders → matched rows.
Customers without orders → order columns are NULL.
Orders without customers → customer columns are NULL.

WINDOW FUNCTIONS

Window functions allow us to calculate values like rankings, running totals, and averages across a group of rows while still keeping each individual row visible.
Core components of windows functions include:

Order by

The SQL ORDER BY clause is used to sort the result set of a SELECT query in a specific order based on one or more columns. Without this clause, there is no guaranteed order for the returned rows.
Example

SELECT * FROM Customers ORDER BY CustomerName;  

Partition by

It is used to divide a query's result set into partitions (groups). The window function then performs calculations, such as aggregation or ranking, within each partition independently, without collapsing the individual rows of the result set.
Example

SUM(amount)   
OVER (PARTITION BY customer_id ORDER BY order_date)   
AS cumulative_sales  

This query calculates a running total of sales for each customer, ordered by date. The sum resets for each new customer ID.

Over()

The OVER clause determines the partitioning and ordering of a rowset before the associated window function is applied.
Example

SELECT   
    employee_name,  
    department,  
    salary,  
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank  
FROM employees;  

This query ranks employees within each department based on salary without grouping the table.

Other key window functions

ROW_NUMBER() - It assigns a unique, sequential integer (starting at 1) to each row within a result set based on a specified ORDER BY clause. It is commonly used for pagination, ranking, and identifying top-N results.

RANK()- It assigns a rank to each row within a result set.

DENSE_RANK()- It assigns a rank to each row within a result set, with tied values receiving the same rank and no gaps in the ranking sequence.

SUM() OVER() (running total)- It is used to calculate the sum of values across a set of table rows related to the current row, without collapsing the individual rows themselves. This differs from a standard SUM() with GROUP BY, which returns only a single summary row per group.

AVG() OVER()- It is used to calculate the average value of a numeric column across a specific "window" or set of related rows, without collapsing the individual rows of the result set into a single summary row.

CONCLUSION

For practical data analysis, SQL joins and window functions are vital tools. Joins enable us to comprehend connections between various datasets, such customers and their orders, and to merge relevant tables. On the other hand, window functions let us to carry out sophisticated computations like running totals and rankings without losing track of specific row information.

When combined, these ideas help us go beyond simple questions and provide more profound analytical understanding. While mastering window functions enables more sophisticated data analysis, mastering joins enables us to connect data.