SQL Joins and Window Functions

SQL joins allows you to collate/add two or more tables together using a common identifier e.g. a foreign key. Due to normalization, one may not get all the required columns in a single table in order to reduce data redundancies.

The common identifier acts as a transportation between the two tables allowing columns from both tables be included in the query 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 is a type of function that performs a calculation across a specific set of rows (the ‘window’ in question), defined by an OVER () clause. Window functions use values from one or multiple rows to return a value for each row, which makes them different from traditional aggregate functions, which return a single value for multiple rows

Syntax

SELECT column_1,

       column_2,

       function() OVER (

           PARTITION BY partition_expression

           ORDER BY order_expression

       ) AS output_column_name

FROM table_name;

Explanation

• 
  SELECT → clause defines the columns you want to select from the table_name table.


• 
  function() → The window function you want to apply.


• 
  OVER() → Defines the window.


• 
  PARTITION BY → divides rows into partitions based on the specified partition_expression; if the partition_expression is not specified, the result set will be treated as a single partition..


• 
  ORDER BY → Defines row order within each partition.


• 
  output_column_name → Name of the calculated column.

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 individual employee records.

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 combining data from multiple tables.


• Use Window Functions when you need row-level results alongside aggregated calculations.