UNDERSTANDING SQL:JOINS & WINDOW FUNCTIONS.

#sql #postgres #datascience

INTRODUCTION
As we dive deeper in sql,lets learn some key functions in sql.
What are joins;
--is used to combine rows from two or more tables, based on a related column between them
--it allows as to work with multiple tables and allow as join data in different tables.
We have different types of joins with their different function.
They include as follows:
** TYPES OF JOINS*
**1.INNER JOIN*
-Is used to combine rows from two or more tables based on a related column. It returns only the rows that have matching values in both tables, filtering out non-matching records

*2.LEFT (OUTER) JOIN*
returns all rows from the left table, and only the matched rows from the right table
Unlike inner join which brings matched rows from all tables,left join only brings from the right table.

3.RIGHT(OUTER) JOIN
Returns all rows from the right table , and only the matched rows from the left table.Its atmost jthe oppossite of left join.
Here the command example;

4.FULL OUTER JOIN
Returns all rows when there is a match in either the left or right table
See two example of commands from my table that can execute the function;

5.SELF JOIN
This is when a table is joined on itself
We use aliases to refer to the same table
Example command line i used on my data table;

6.CROSS JOIN
Creates returns every combination of rows from both tables
Example of command;

These all about joints.As seen i have highlighted 6 ty
pes.

WINDOW FUNTIONS
Apparently window functions allow you perform calculations across set of rows that are related to the current row.
We have different types of window functions,
these are; >ROW NUMBER-assigns a unique sequential number to each row.
>SUM () OVER()-Running or partitioned totals
>AVG() OVER()-Running or partioned averages
>RANKS-Assigns a rank with gaps for ties
>DENSE RANK-Assigns rank without gaps for ties.
Below is how i used window funtions in assigning Ranks.;