JOINS
The JOIN clause is used to retrieve data from multiple tables based on logical relationships between them. Joins are fundamental to relational database operations and enable you to combine data from two or more tables into a single result set.
TYPES OF JOINS
1. Inner Join
It returns only rows that have matching values in both tables.
In SQL the join and inner join clause are the same
2. Right Join
It returns all rows from the right table, and only the matched rows from
the left table
3. Left Join
It returns all rows from the left table, and only the matched rows from the right table
4. Full Join
It returns all rows when there is a match in either the left or right table
WINDOW FUNCTIONS
A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a group of rows, window functions retain the identity of individual rows.
The basic word that turns a regular function into a window function is OVER.
LEAD FUNCTION
It provides access to a row at a specified physical offset that follows the current row.

LAG FUNCTION
It provides access to a row at a specified physical offset which comes before the current row.

NTILE FUNCTION
It allows you to break the result set into a specified number of approximately equal groups, or buckets by assigning each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

PARTITION BY FUNCTION
This clause divides the result into partitions to which the window function is applied

Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK
Ranking is one of the most common use cases. Imagine you have a list of students and their scores, and you want to see who came in first.
SELECT
student_name,
score,
ROW_NUMBER() OVER(ORDER BY score DESC) as row_num,
RANK() OVER(ORDER BY score DESC) as rank_val,
DENSE_RANK() OVER(ORDER BY score DESC) as dense_rank_val
FROM students;
-
ROW_NUMBER(): Assigns a unique, sequential number to each row (1, 2, 3, 4). -
RANK(): If two rows have the same score, they get the same rank, but the next rank is skipped (1, 2, 2, 4). -
DENSE_RANK(): Similar to rank, but it doesn’t skip any numbers (1, 2, 2, 3).




Top comments (0)