Joins and Windows Function in Sql.

Introduction

As a data analyst or Data engineer mastering Sql narrows down to getting into advanced concepts that will enable one to manage, query and optimize databases. In this article we will explore Joins and windows functions in sql.

Joins

Joins bringing together information from different tables turning a database into a unified space where data relationship becoming the building for effective data analysis

Types of joins

Inner Join
This join combines two or more tables and returns only rows that have matching values in the tables.
For example look for an order in the orders table

Then customer table

The relationship between the two tables is the customer_id.To create an inner join use this;

SELECT Orders.order_id , Clients.first_name, Orders.order_date
FROM Orders
INNER JOIN Clients ON Orders.customer_id=Clients.Customer_id;

it will display something like

Left Join

The left join returns all rows from the left teble and only the matched rows from the right table, if the is no match in the right table the result from the right table will be null.
For example using the selection of customers and orders table used in the inner join, we can create a left join using the following syntax

SELECT Clients.last_Name, Orders.order_id 
FROM Clients
LEFT JOIN Orders ON Clients.customer_id  = Orders.customer_id 
ORDER BY Clients.last_Name;

The result should be;

Right Join

The right join is the opposite of the left join it shows all rows from the right table and matched rows from the left table.
For example using the selection of customers and orders table used in the inner join, we can create a right join using the following syntax

SELECT Orders.Order_id, clients.last_Name, clients.first_Name
FROM Orders
RIGHT JOIN clients ON Orders.customer_id = clients.customer_id 
ORDER BY Orders.order_id;

The result should be

Full Join

It returns all rows when there is a match in either the left or right table, if the rows on the left have no match on the right,the result inludes data of the left table and null on the right table and vice versa.

For example using the selection of customers and orders table used in the inner join, we can create a full join using the following syntax

SELECT Clients.first_Name, Orders.order_id 
FROM clients
FULL JOIN Orders
ON Clients.customer_id  = Orders.customer_id

The result should be

Self Join

This is a regular join where a table is joined by itself. it is used to compare rows within the same table or find related records in a single table

Windows function in Sql

Windows function in Sql are a feature that allows one to perform calculations across a set of of table rows related to the current table without collapsing the result into a single value.
The basic sythax for a window function is

function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]

Types of windows functions.

we have two types of windows function aggregate and ranking window functions.

Aggregate function.

It calculates aggregates over a window of rows while retaining individual rows
some of the aggregate functions include:

• Sum() - sums values within a window.
• Count() - counts rows within a window.
• Max() - Returns the maximum value in a window.
• Min() - returns the minimum value in a window.
• Average() - calculates the average value in a window.

Examples using average; we will calculate average salary within departments. Using the following sythax

SELECT first_name, hire_date , department, salary, 
       AVG(salary) OVER( PARTITION BY department) AS Avg_Salary
 FROM employees

The result should be

Ranking window function.

They are used to assign rank to each rows. common ranking functions include;

• Rank() - assigns ranks to rows skipping duplicates in the rows.
• Dense rank() -assigns ranks to rows without skipping duplicates. -Row_Number - assigns a unique number to each row in the result set.
• Percent_Rank - shows the relative rank of a row as a percentage between 0 and 1. for example using the same employees table we are going to rank employees within their departments using their salary We will use the following synthax;

select 
    employee_id, 
    first_name, 
    last_name, 
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

The result should be;