Joins and Window Functions in SQL

SQL (Structured Query Language) is essential for working with relational databases. Two powerful tools in SQL are Joins and Window Functions. They allow us to combine data from multiple tables and perform complex calculations efficiently.

JOINS
Joins are the foundation of multi table query processing in SQL.
It is a clause used to combine rows from two or more tables.
Joins allow us to work with multiple tables and allows us to join data from different tables.
We need to have a primary key and a foreign key for us to use joins.
Primary and foreign keys allows us to reference from our tables uniquely.
Types of Joins
EMPLOYEE TABLE

DEPARTMENT TABLE

We are going to use the above tables to understand joins.

1.INNER JOIN
Returns only the rows that match or matching values in both tables.
If you want to find the rows with matching values on both the tables you do an Inner Join using the SQL query below;

Result of the query;

2.LEFT JOIN
Returns all the rows from the left table and the matching rows from the right table
For example,lets use the LEFT JOIN to find all employees and their respective departments.
SQL Query;

Query results;

3.RIGHT JOIN
This join returns all rows from the right table.
If a row in the right table has no corresponding match in the left table, the columns from the left table will contain Null values in the result set.
Our table 'Employees' and 'Departments' has a shared column 'department_id', we can do a RIGHT JOIN to find all departments and their respective employees.
SQL Query;

Result query;

4.FULL OUTER JOIN
Combines both left and right join and it shows all rows from the table. If their are no matches it will contain a NULL.
Example;
Let's do a full outer join to find the list of employees and department.
SQL Query;

Result query;

7.NATURAL JOIN
Joins all tables using columns that have the same name.
select * from employees natural join departments;

WINDOW FUNCTIONS
SQL window functions perform calculations across a set of related rows (a "window") and return a single value for each original row, without collapsing the rows. They contrast with standard aggregate functions that return a single value for an entire group of rows.

Window Syntax
OVER() - defines the window
PARTITION BY - splits data into groups
ORER BY - defines calculation order

1.ROW_NUMBER() FUNCTION
Assigns each number to row.
Ranks without ties.
Lets assign the row numbers to the sales;

Result;

2.RANK()
Assigns ranks to rows, leaves gaps in the ranking where their are ties, within a window.
For example, let's assign row number to orders based on quantity from highest to lowest
SQL Query;

Result of query;

3.DENSE RANK()
Assigns the same rank to tied values.
Does not skip rank numbers.
For example let's assign row number to orders based on quantity from highest to lowest.
SQL Query;

Result Query;

Aggregate Window Functions
SUM()
Calculates totals across a partition.
AVG()
Computes average values over a window.
COUNT()
Counts rows within a partition.
MIN()
Returns minimum value in a window..
MAX()
Returns maximum value in a window

Navigation (Value) Functions
LEAD()
Retrieves value from a next row.
LAG
Retrieves value from the previous row.

Summary
Joins combine tables using a related column.
Window Functions perform calculations across a window of rows.