SQL JOINS AND WINDOW FUNCTIONS

Source: Dev.to

Introduction

SQL joins allow you to combine two or more tables using a common identifier (e.g., a foreign key). Because of normalization, a single table may not contain all the columns you need, so joins “transport” data between tables, letting you include columns from both tables in the result set.

Sample Tables Used for Illustration

Student Table

Course Table

Types of SQL Joins

1️⃣ INNER JOIN

Returns only the rows that exist in both tables.

SELECT name, course_name
FROM Student
INNER JOIN Course
  ON Student.course_id = Course.course_id;

Output

2️⃣ LEFT JOIN

Returns all rows from the left table and matching rows from the right table.

SELECT name, course_name
FROM Student
LEFT JOIN Course
  ON Student.course_id = Course.course_id;

Output

3️⃣ RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT name, course_name
FROM Student
RIGHT JOIN Course
  ON Student.course_id = Course.course_id;

Output

4️⃣ FULL JOIN

Returns all rows from both tables, matching where possible.

SELECT name, course_name
FROM Student
FULL JOIN Course
  ON Student.course_id = Course.course_id;

Output

Window Functions

A window function performs a calculation across a specific set of rows (the “window”) defined by an OVER () clause. Unlike traditional aggregate functions, which collapse rows into a single result, window functions return a value for each row while still having access to other rows in the defined window.

Syntax

SELECT column_1,
       column_2,
       function() OVER (
           PARTITION BY partition_expression
           ORDER BY order_expression
       ) AS output_column_name
FROM table_name;

Explanation

Example

SELECT dem.first_name,
       dem.last_name,
       gender,
       AVG(salary) OVER (PARTITION BY gender) AS avg_salary_by_gender
FROM employee_demographics dem
JOIN employee_salary sal
  ON dem.employee_id = sal.employee_id;

This calculates the average salary for each gender while still displaying each individual employee’s record.

Commonly Used Window Functions

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG(expression, offset)
• FIRST_VALUE()
• LAST_VALUE()

Aggregate Functions Used as Window Functions

• SUM()
• AVG()
• MAX()
• MIN()
• COUNT()

Conclusion

• Use JOINs when you need to combine related data from multiple tables.
• Choose the appropriate join type (INNER, LEFT, RIGHT, FULL) based on which rows you want to keep.
• Use window functions when you need row‑level calculations that depend on a set of related rows (e.g., running totals, rankings, moving averages).
• Pair PARTITION BY with ORDER BY to fine‑tune the window scope and ordering.

These tools together make SQL a powerful language for both data retrieval and analytical calculations.