Introduction to Joins and Windows Funtions in SQL

#database #datascience #luxdevhq #dataengineering

Introduction

Joins define relational operations that primarily combine data from multiple tables based on a logical relationship which in most cases is a foreign key, potentially increasing row counts and expanding columns. When working with databases, specifically normalized databases, it is a best practice to distribute data across multiple tables subsequently enforcing integrity and eliminating redundancy. The different types of joints include the INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

INNER JOIN
This is the default join type that returns only matching records from the referenced tables in the query.
Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

LEFT JOIN
Returns all rows from the left table (the first table) and matched rows from the right (second table), with NULLs for unmatched right-side rows.
Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN
Returns all rows from the right table (the second table) and matched rows from the left (first table), with NULLs for unmatched left-side rows.
Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

FULL JOIN
This type of join returns all rows from both tables with non-matches on either side returned as null values.
Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

**CROSS JOIN
**This is a special join type that allows users to produce a Cartesian product, pairing every row from the first table with every row from the second. However, when using this join you should be careful to avoid row explosion.
Example

SELECT s.full_name, c.course_name
FROM students s
CROSS JOIN courses c;

WINDOWS FUNCTIONS

SQL window functions enable calculations over a group of rows associated with the current row, while preserving each individual row in the result set rather than aggregating them into a single summary value. We can think of windows functions as commands to analyze rows around me without merging or removing rows. The different windows functions are commonly used for tasks like aggregates, running totals, and ranking as they provide a holistic view of the data.
Below are the common SQL window functions presented in bullet format, organized by catego

Ranking Functions

ROW_NUMBER()
Assigns a unique sequential number to each row within a partition
No ties allowed
RANK()
Assigns the same rank to tied values
Skips rank numbers after ties
DENSE_RANK()
Assigns the same rank to tied values
Does not skip rank numbers

Aggregate Window Functions

SUM()
Calculates totals across a partition
AVG()
Computes average values over a window
COUNT()
Counts rows within a partition
MIN()
Returns minimum value in a window
MAX()
Returns maximum value in a window

Navigation (Value) Functions

LAG()
Retrieves value from a previous row
LEAD()
Retrieves value from a following row
FIRST_VALUE()
Returns the first value in a window
LAST_VALUE()
Returns the last value in a window

Conclusion.

In summary, joins and window functions serve distinct yet complementary roles in SQL querying. Joins integrate data across related tables, enabling comprehensive relational analysis, while window functions enhance datasets with analytical insights without reducing row-level detail. Mastery of both concepts is essential for designing efficient queries, supporting advanced reporting, and performing meaningful data analysis in structured, normalized database environments.

DEV Community