Joins and Windows Functions in SQL

INTRODUCTION

Data in relational databases is usually stored in different tables. Joins allows one to combine data from multiple tables whereas window functions allow calculations across related rows without grouping the results into a single row.

1. JOINS
Joins are operations that allow one to combine rows from two or more tables based on a related column between them.

Types of Joins
-INNER JOIN
The most common type of join. It returns only the rows that have matching values in both tables based on a related column between them.It can also be written as 'join'.
- LEFT JOIN
Returns all rows from the table on the left(the first selected table) and matching rows from the right table. Suppose there is no match, NULL values are returned.

- RIGHT JOIN
Returns all rows from the right table(the second table selected) and matching rows from the left table. Suppose there is no match, NULL values are returned.

- FULL OUTER JOIN
Returns all rows from both tables. Non-matching rows from both tables will contain NULL values.

2. WINDOW FUNCTIONS
Window functions are functions that perform operations across a set of rows that are related to the row the function is currently operating on.
Types of Window Functions

• ROW_NUMBER() – assigns a unique number to each row
• RANK() – assigns ranks to rows with gaps for duplicates
• DENSE_RANK() – assigns ranks without gaps for duplicates
• LAG() – accesses previous row values
• LEAD() – accesses next row values

The OVER() Clause
All window functions require the OVER() clause. This clause defines the window of rows the function should operate on. You can specify how rows are grouped with PARTITION BY and the order of rows with ORDER BY.
For example, if one wants to rank sales by each region, they use a ranking function with OVER(PARTITION BY region ORDER BY sales DESC). The OVER() clause is what makes these functions “window functions” instead of ordinary aggregates.

CONCLUSION

Joins and window functions are key tools in SQL that make working with data easier. Joins let you combine information from different tables, so you can see how data relates. Window functions let you do calculations across rows without losing the details of each row. Learning how to use both makes your queries more powerful and helps you get better insights from your data.