JOINS AND WINDOWS FUNCTIONS IN SQL

#sql #windowsinssql

STRUCTURED QUERY LANGUAGE
Mostly referred to as SQL, it's the standard programming language designed for managing, manipulating, and retrieving data stored in relational databases.
In this article, we will focus on the joins and windows functions.

JOINS

Essential operations in SQL that merge rows from different tables and bring information together. you can also say that they combine columns from multiple tables based on a related key, enabling data retrieval from a normalized database structure.
TYPES OF JOINS

1. INNER JOIN
Returns only rows that have matching values in both tables.
Example student table(table 1)and subject table(table 2)
Student table

Student ID	Name
1	Maki
2	Mani
3	Mapi

Subject table

EnrollmentID	Student ID	Subject Name
111	1	History
112	2	Geography
113	4	Business

The INNER JOIN Query would be
SELECT Student.StudentName,Subject.SubjectName
FROM Student
INNER JOIN Subject
ON Student.Student ID = Subject.Student ID

Results

StudentName	Subject Name
Maki	History
Mani	Geography

INNER JOIN returns only rows where the join condition matches in both tables; For Maki and Mani, a match was found in the student ID (1,2) but for Mapi, student ID (3) no enrollment hence excluded and studentID 4 no matching student was found also.
2. LEFT JOIN
Returns all rows from the left table and matching rows from the right table. If a row in the left table does not have a match in the right table, the columns from the right table will contain NULL values in the result. Left join records all from the left table and matching records from the right table.
Example Teacher _and _Classes
Teacher Table(Left table)

TeacherID	Name	ClassID
1	Cece	01
2	Gordon	02
3	Forrest	03
4	Frank	04

ClassID	ClassName
01	Junior
02	Midlevel
03	Senior

LEFT JOIN QUERY
SELECT Teacher.Name, Class.ClassName
FROM Teachers
LEFT JOIN Classes
_ON _Teacher.ClassID = Class.ClassID;

Results

Name	ClassName
Cece	Junior
Gordon	Midlevel
Forrest	Senior
Frank	NULL

NB LEFT JOIN takes all rows from left table, even if no match

3. RIGHT JOIN
It is the opposite of LEFT JOIN-returns all rows from the right table and matched rows from the left, with NULL for non-matches.
4. FULL JOIN
Also known as FULL OUTER JOIN. It combines LEFT and RIGHT JOINS. It returns all rows from the right and left tables, regardless of whether there is a match or not.
example
Person Table

CitizenID	Name
1	Aaron
2	Sheryl
3	Bitu

Sport Table

CitizenID	Sport
1	Rugby
2	Basketball
4	Fencing

Full Join Query
SELECT Person.Name, Sport.Sport
From Person
FULL JOIN Sport
ON Person.CitizenID = Sport.CitizenID
Results

Name	Sport
Aaron	Rugby
Sheryl	Geography
Bitu	NULL
NULL	Fencing

5. CROSS JOIN
Produces the Cartesian product of two or more tables, combining every row from the first table with every row from the second table. It does not require a join condition.
Example
Assume you have two tables: Colors and Sizes.
Colors Table:
color_name
Red
Blue
Sizes Table:
size_name
Small
Medium
Large
The SQL query would look like this:
SELECT Colors.color_name, Sizes.size_name
FROM Colors
CROSS JOIN _Sizes;
The result of the query would be:
color_name size_name
Red Small
Red Medium
Red Large
Blue Small
Blue Medium
Blue Large
6. SELF JOIN
Where a table is joined with itself. Useful when a table contains related data within itself. You must use table _aliases to differentiate two copies of the same table.
Example
Find workers who drive the same car in the company
Workers Table

WorkersID	Name	Car
1	John	Hilux
2	Salome	Ipsum
3	Daud	Hilux
4	Yakov	Fit
5	Sarah	Ipsum

SELF JOIN QUERY
SELECT
W1.Name AS Worker1,
W2.Name AS Worker2,
W1. Car
FROM Workers W1
INNER JOIN Workers W2
ON W1.Car = W2.Car
AND W1.WorkersID < W2.WorkerID
Result

Workers1	Workers2	Car
John	Daud	Hilux
Salome	Sarah	Ipsum

Both John and David have a Hilux, Salome and Sarah have the same car.

WINDOWS FUNCTIONS

They maintain the original row count, adding computed values as new columns.

Components of window Function

Function:: Like ROW_NUMBER(), RANK(), SUM() OVER() Clause: Sorts rows within each partition.

Types of Window Functions

1.RANKING FUNCTIONS-Assign unique numbers or ranks based on specific ordering. It assigns ranks or numbers to rows.

ROW_NUMBER(): Unique sequential function.
RANK(): Rank with ties skipping numbers(example 3,4,4,6)
DENSE_RANK(): Ranks with ties without skipping(example 1,2,2,3)
AGGREGATE FUNCTIONS- Performs calculations like SUM,AVG,MIN, MAX
VALUE FUNCTIONS- Access other rows' values. -LAG(): Value from a previous row. -LEAD(): Value from a next row -FIRST_VALUE()/LAST_VALUE(): First or last Value in the window

Being knowledgeable about joins and windows functions transforms SQL from a simple query language to an analytical tool.