Joins are used to combine rows from two or more tables based on a related column between them. This allows you to retrieve connected data stored across multiple tables in a single result.
Types of joins.
1: INNER JOIN
INNER JOIN returns only matching rows from both tables. If there is no match between the tables the row is excluded from the result. It is used when you only want records that exist in both tables or want to combine related data.
2: LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table. Also known as left outer join. If no match exists null values are returned for the right table columns.
3: RIGHT JOIN
RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists null values are returned for the left table columns.
4: FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. If no match exists nulls appear on the missing side. It is used for combining two datasets and finding mismatches between systems.
5: CROSS JOIN
CROSS JOIN returns the cartesian product - every row from the first table combined with every row from the second table. No matching is required.
WINDOW FUNCTIONS IN SQL.
Window functions perform calculations across a set of rows related to the current row without grouping the rows into a single output. They preserve the individual row details while providing additional contextual insights.
Key components.
Window functions are defined using the mandatory OVER() clause which specifies how the rows are partitioned and ordered for the calculation.
Common types of window functions.
1: RANKING - Row_number, rank, dense_rank - It assigns numbers or ranks to rows based on order.
Rank
Row number
2: AGGREGATE - sum, avg, min, max, count - calculates sum, averages or extremes across the window.
Total
Average
Count
Max
3: VALUE/OFFSET - lag, lead - Accesses data from rows before, after or at specific points in the window.
lag
Used to compare rows i.e. comparing current value to previous value
lead
looks forward to the next row or a specified number of rows ahead.
Top comments (1)
🚀🚀