DEV Community

tonny otieno
tonny otieno

Posted on

SQL JOINS AND WINDOW FUNCTIONS

#beginners #database #sql #tutorial

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

student_id name course_id
1 Willis Kip 001
2 Edwin Sifuna 002
3 Tonny Oti NULL

Course Table

course_id course_name
001 Botany
002 Political_sci
004 English

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;
Enter fullscreen mode Exit fullscreen mode

Output

Name Course_name
Willis Kip Botany
Edwin Sifuna Political_sci

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;
Enter fullscreen mode Exit fullscreen mode

Output

Name Course_name
Willis Kip Botany
Edwin Sifuna Political_sci
Tonny Oti NULL

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;
Enter fullscreen mode Exit fullscreen mode

Output

Name Course_name
Willis Kip Botany
Edwin Sifuna Political_sci
NULL English

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;
Enter fullscreen mode Exit fullscreen mode

Output

Name Course_name
Willis Kip Botany
Edwin Sifuna Political_sci
Tonny Oti NULL
NULL English

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)