Learning how to use Windows SQL Functions and Joins in Relational Databases.

This article explains how Windows Functions and SQL Joins are used and their importance for querying data from databases.

Rows from two or more tables can be combined using a JOIN clause, depending on a linked column between them.

Several types of joins help in data query and manipulation in SQL. These varieties are discussed as follows:

INNER JOIN
Entries with matching values in both tables are chosen using the INNER JOIN keyword.
In reference to a table, this can be illustrated as follows:-

A good example illustrating an INNER JOIN is as follows:

select o.order_id, c.first_name,c.last_name
from customers c
inner join orders o
on o.customer_id =c.customers_id;

NOTE:-
As long as the columns match, the INNER JOIN keyword selects every row from both tables.
Orders will not be displayed if there are records in the "Orders" table that do not match those in the "Customers" table.

RIGHT JOIN
This is an important query command in SQL.
All of the records from the right table (table2) and any matching records from the left table (table1) are returned using the RIGHT JOIN keyword.

The tables being highlighted are shown below:

An illustration of this is as follows:

SELECT 
    c.customer_id,
    c.name,
    c.city,
    COALESCE(SUM(o.amount), 0) AS total_spent,
    COUNT(o.order_id) AS order_count
FROM customers c
RIGHT JOIN orders o 
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.city;

LEFT JOIN

All of the records from the left table (table1) and any matching records from the right table (table2) are returned using the LEFT JOIN keyword.

CROSS JOIN
This type of join is as illustrated below:-

The Cartesian product of two or more tables is returned by the CROSS JOIN keyword, which combines each record from the first table with each row from the second table.

NOTE:-
Therefore, if the first table has 50 rows, and the second table has 2500 rows, the result set will be 50x2500 rows.

WINDOWS FUNCTIONS IN SQL
Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row.
Unlike an aggregate function like SUM(), which returns one result for a group of rows, window functions return a value for each row while still providing information from the related rows.

Types of Windows Functions

A. ROW_NUMBER () function
This function assigns a unique number to each row, starting from 1, based on the order specified by the ORDER BY clause.

B. LAG () function
This function will help query values from the past data from the previous row, e.g., if you want to compare the current lead time in days and the previous lead time in days.

C. RANK () function
You can use this function to rank your data in either descending or ascending order. In a tie, it will bypass the subsequent rank value. For example, the following record will be placed fourth rather than third if two rows are tied at rank 2.

D. NTILE() function
You can use this function to split a set of rows into specified, roughly equal portions.
For example, NTILE(3) will split the chosen rows into three roughly equal parts; by roughly, we mean that the number of rows in each portion may vary.

E. DENSE_RANK() function
This function helps to rank - in that, it does not leave gaps, so if there are ties, the next rank will be consecutive.

Example
If two customers are tied for rank 1, the next customer will be ranked 2nd.

I have found it great to keep on learning!
Here are my thoughts on this subject.
I cannot wait to interact with you on this topic!
Keep it here for more!