SQL JOINS AND WINDOW FUNCTIONS WITH CASE EXAMPLE

SQL JOINS

When working with relational databases, data is often split across multiple tables. SQL Joins is used to combine data from two or more tables based on related column into one result.
It helps in:

1. Matching records using related columns.
2. Retrieving connected data from multiple tables.
3. Improving data analysis by combining related information.

Types of SQL Joins
SQL joins are categorized into different types based on how rows from two tables are matched and combined.
For this article, we will use two tables. i.e

STUDENT TABLE and COURSE TABLE

For Student table, we create the table first, then we Insert values as shown below:

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    First_name VARCHAR(50) NOT NULL,
    Last_name VARCHAR(50) NOT NULL,
    Gender CHAR(1),
    Email VARCHAR(100) UNIQUE
);

INSERT INTO Students (Student_ID,First_name, Last_name, Email, Gender)
VALUES 
(201,'Alice', 'Johnson', '[e-mail]', 'F'),
(202,'Robert', 'Smith', '[e-mail]', 'M'),
(203,'Elena', 'Rossi', '[e-mail]', 'F'),
(204,'Kenji', 'Sato', '[e-mail]', 'M'),
(205,'Zara', 'Khan', '[e-mail]', 'O');

Result:

For Course table, we create the table first, then we Insert Values as shown below:

CREATE TABLE Courses (
    Course_ID INT PRIMARY KEY,
    Student_ID INT,
    Full_name VARCHAR(100),
    Last_name VARCHAR(50),
    Course VARCHAR(50),
    FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
);

INSERT INTO Courses (Course_ID, Student_ID, Full_name, Last_name, Course)
VALUES 
(1001, 201, 'Alice Johnson', 'Johnson', 'Data Science'),
(1002, 202, 'Robert Smith', 'Smith', 'Graphic Design'),
(1003, 203, 'Elena Rossi', 'Rossi', 'Architecture'),
(1004, 204, 'Kenji Sato', 'Sato', 'Artificial Intelligence'),
(1005, 205, 'Zara Khan', 'Khan', 'Cyber Security');

Result:

1. SQL Inner Join It is used to retrieve rows where matching values exist in both tables.

Consider the two tables, Students and Course, which share a common column Student_ID.

Using SQL inner Join, we combine data from these tables based on their relationship, allowing us to retrieve meaningful information like student details along with their enrolled courses.

Query:

SELECT 
    Students.Student_ID, 
    Students.First_name, 
    Students.Last_name, 
    Students.Email, 
    Courses.Course
FROM Students
INNER JOIN Courses ON Students.Student_ID = Courses.Student_ID;

Result:

This helps in:

• Combining records based on related column.

• Returning only matching rows from both tables.

• Excluding non-matching data from the result set.

2.SQL Left Join
It is used to retrieve all rows from the left table(Students) and matching rows from the right table(Course).

Query:

SELECT 
    Students.Student_ID, 
    Students.First_name, 
    Students.Last_name, 
    Courses.Course
FROM Students
LEFT JOIN Courses ON Students.Student_ID = Courses.Student_ID;

Result:

This helps in:

• Returning all records from the left table.
• Showing matching data from the right table.
• Displaying NULL values where no match exists in the right table.

3.SQL Right Join
It is used to retrieve all rows from the right table(Course) and matching rows from the left table(Students).

Query:

SELECT 
    Students.Student_ID, 
    Students.First_name, 
    Students.Last_name, 
    Courses.Course_ID,
    Courses.Course
FROM Students
RIGHT JOIN Courses ON Students.Student_ID = Courses.Student_ID;

Result:

This helps in:

• Returning all records from the right table.

• Showing matching data from the left table.

• Displaying NULL values where no match exists in the left table.

4.SQL Full Join
Its is used to combine the results of both right join and left join.

Query:

SELECT 
    Students.Student_ID, 
    Students.First_name, 
    Courses.Course_ID,
    Courses.Course
FROM Students
FULL JOIN Courses ON Students.Student_ID = Courses.Student_ID;

Result:

This helps in:

• Returning all rows from both tables.

• Displaying NULL values where no match exists in either table.

• Providing complete data from both sides of the join.

5.SQL Natural Join
It is a type of Inner Join that automatically joins two tables based on columns that have the same name and data type in both tables.

Query:

SELECT Student_Id,First_name,Last_name,gender,course_id,course
FROM Students
NATURAL JOIN Courses;

Result:

Summary
By combining rows from multiple tables based on related columns, SQL joins allows you to transform fragmented data into a comprehensive and actionable result.

SQL Window Functions

It is a set of SQL Functions that allows you to perform calculations over a set of rows without needing to group by each row.
To explain window function, we must understand its syntax. The basic syntax is as follows:

window_function_name([ALL] expression)     
OVER (    
    [partition_defintion]    
    [order_definition]   
)  

• window_function-This indicates the name of our window function.

• Over()- It contains two expression. i.e. partition by and order by clauses which are responsible for dividing rows into partitions in relation to given criteria before performing calculations.

• Partition by -This divides up our rows into partitions based on the given criteria.

• Order by -It is used to determine the order of values based on each partition.

Types of Windows Functions
There are three different types of SQL window functions. They include:

1. Aggregate Window function: This allows to perform operations on set of rows within a window. e.g. SUM(),MAX(),MIN(),AVG() or COUNT().

2. Ranking Window Function: This helps to ranks the rows within a given window. e.g. RANK(),DENSE_RANK() or ROW_NUMBER().

3.Value Window Function(): This combines multiple operations into one. Can be found through LAG(),LEAD(),FIRST_VALUE() among others.

We are going to create a table in our database called employee_salary to perform SQL Window functions.

Query:

CREATE TABLE employee_salary (
  employee_id SERIAL PRIMARY KEY,
  employee_name VARCHAR(30),
  department VARCHAR(40),
  salary INT
);

INSERT INTO employee_salary VALUES (1, 'Sam', 'IT', 5000);
INSERT INTO employee_salary VALUES (2, 'Sarah', 'Finance', 6000);
INSERT INTO employee_salary VALUES (3, 'Michael', 'HR', 5500);
INSERT INTO employee_salary VALUES (4, 'Emily', 'Marketing', 5200);
INSERT INTO employee_salary VALUES (5, 'David', 'IT', 4800);
INSERT INTO employee_salary VALUES (6, 'Rose', 'Finance', 6100);

Result:

Using Aggregate Window Function

• To compare the highest and lowest salary from all records, we will use Max() and MIN() function respectively.

Query:

SELECT employee_name,department,salary, 
MAX(Salary) OVER() as highest_salary,
MIN(Salary) OVER() as lowest_salary
FROM employee_salary;

Result:

• To calculate the average salary within each department, we will use the AVG() function.

Query:

SELECT employee_name, department,salary,
AVG(Salary) over(PARTITION BY department) as avg_salary
FROM employee_salary;

Result:

Using Ranking Window Function

• Using ROW_NUMBER(), we will assign a unique number to each row based on salary order.

Query:

SELECT employee_name, Department, Salary,
       ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_row_no
FROM employee_salary  ;

Result:

• Using RANK(), we will rank employees within their department based on salary in descending order.

Query:

SELECT *,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS department_rank
FROM employee_salary;

Result:

• Using DENSE_RANK(), we will rank employees by salary without skipping ranks.

Query:

SELECT employee_name, Department, Salary,
       DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_dense_rank
FROM employee_salary ;

Result:

• Using PERCENT_RANK(), we will find the relative salary position of each employee within a department.

Query:

SELECT employee_name, Department, Salary,
       PERCENT_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_percent_rank
FROM employee_salary;

Result:

Using Value Window Function

• Using LAG(), we will get employee's previous salary within their department.

Query:

SELECT*,
    LAG(salary) OVER(PARTITION BY department ORDER BY employee_id) AS previous_salary
FROM employee_salary;

Result:

Summary
SQL Window functions are powerful tools that allow for complex calculations and analysis without the need for subqueries or complex joins.
They provide flexibility with partitioning and order clauses, enabling data access from multiple rows to calculate aggregates, rank rows and perform various tasks within groups more efficiently.