JOINS & WINDOW FUNCTIONS.

Joins are sql clauses that combine two or more tables using a related column that exists between the tables. That way, data that is stored in different tables but is related is retrieved and can be processed and analysed to give meaningful results.
There are several types of joins as follows:

1. Inner join

An inner join returns only rows where there is a match in both tables based on a condition. Consider the following example;

consider a transaction table and a customer table.

we use;
select *
from transaction inner join customers
on transaction.customer_id = customers.customer_id

the customer_id i this case is the common column between the two tables;

on exacution, the new table looks as follows:

There is a return on every instance the customer_id has a match on bith tables.

2. Left outer join

The left outer join returns all rows from the left table and matching rows from the right table. Rows that do not match from the right table return nulls.

3. Right outer join

The right outer join returns all rows from the right table and matching rows from the left table. Any non matching row from the left returns a null.

4. Full outer join

A full outer join returns all rows from both tables with null values where no match exists.

5. Cross join

This join combines every row from the first table with every row from the second table.

WINDOW FUNCTIONS.

These are sets of tools that perform calculations across a set of rows that are related and do not collapse the results into a single output row, unlike traditional aggregate functions.
Their main components are as follows;

1. Over clause It defines the window of rows over which the function operates.
2. Partitioning by This clause divides results into groups e.g divide by region or department. 3.order by This clause specifies the order of rows within each partition.

Common window functions

Row number()
It assigns a unique sequential number to each row within a partition.
Rank()
It assigns rank within a partition when gaps occur. For example when there is a tie.
Dense rank()
It assigns rank without a gap.