JOINS AND WINDOW FUNCTIONS IN SQL.

SQL stands for structured Query Language and is the language used to communicate with a database.
What is a database ?
An organized collection of data stored electronically.
Data is stored in tables inside databases.
SQL is used to retrieve data,update and analyse data.

JOINS IN SQL

Joins are used in SQL to combine rows from two or more tables based on a common relationship(primary key)
TYPES OF JOINS

• INNER JOIN It returns rows that are same from both tables.

EXAMPLE

From the records this are the matching rows from both tables.

• LEFT JOIN A Left join returns all rows from the left table and the matching rows from the right(second) table. If there are no matching values,NULL values are returned from the right table.

EXAMPLE

• RIGHT JOIN A right join returns all rows from the right table and the matching rows from the left table. If there is no match ,NULL values are returned for the columns from first(left) table.

EXAMPLE

From the records only four rows in the name column are returned, some departments do not have a match hence the NULL values are returned after performing the query.

• Full join It combines the results of a left and right join. A full join returns all rows from both tables, matching rows that meet conditions and includes unmatched rows too.

EXAMPLE

NB Some databases like MySQL,MariaDB and SQLite do not support full join and instead a UNION operator is used to join a left and right join.

• SELF JOIN In this join operation a table is joined with itself. It is used when you want to combine rows from the same table based on a related column.

Joins are very important in SQL since they allow combining of data from different tables,they also reduce duplication by allowing data to be stored in separate tables and they also improve data flexibility.

WINDOW FUNCTIONS IN SQL

Window functions perform calculations on related rows and keeps each row in the result unlike GROUP BY which groups the rows together.
A window function allows viewing of a group of rows.
The OVER() Clause is used in window functions.
Examples of window functions

• ROW_NUMBER()
• RANK()
• RUNNING TOTAL

• AVG() OVER()

• ROW_NUMBER.
  It assigns a unique number to each row based on a condition.
  In the case of 2 rows having similar values, this window function issues different numbers to the given rows.

• RANK. This window function issues a ranking number to each row based on the given conditions. If two rows share the same value, they are assigned with the same value and the next number is skipped.

• RUNNING TOTAL. This is a cumulative sum that adds the current value to previous values based on a specified order.

• AVG() OVER() It calculates average without removing rows.

POINT TO REMEMBER

• PARTITION BY. It is a part of the window function that divides data into groups before calculations. It divides the results in groups just like GROUP BY but does not remove rows.

CONCLUSION
Window function and joins are very important in data analysis ,without which data analysis would be cumbersome.Joins allow you to combine data from different tables for effective analysis and window functions enable you to keep every detail of your data.