Join Operations.
Joins Joins are used to combine data fromtwo or more tables based on related column(s) between them.
The result of a join is a new table that includes columns from both tables, arranged side by side.
Types of join operations
INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN
SELF JOIN
NATURAL JOIN
INNER JOIN. This is a type of join that returns only the rows from both tables that returns only from both tables where there is a match the specified columns in each table. It filters out the rows that do not have corresponding matches in both tables.
SELECT
Columns
FROM table 1
INNER JOIN table 2
ON table1.Column_name = table2.Column_name;
Example; List all the orders made and the names of customers that made them.
LEFT JOIN. LEFT JOIN is also known as LEFT OUTER JOIN. Returns all the rows from the left table and only matching rows from the right table. If there is no match in the right table, null values are returned for the columns of the right table.
SELECT
Columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example; List all employees and all projects assigned.
RIGHT JOIN Right join is also known as RIGHT OUTER JOIN, returns all rows from the right table and the matching rows from the left table. If there is no match from the left table, NULL values are returned for the columns from the left table.
SELECT
Columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Example; List all employees and their departments.
FULL OUTER JOIN A FULL OUTER JOIN returns all the rows from both tables, including unmatched rows from both the left and right tables. If there is no match in either table, NULL values are returned for the columns of the respective table.
SELECT
Columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Example; List all employees and all projects assigned.
CROSS JOIN A CROSS JOIN returns every combination of rows from both tables
SELECT *
FROM table1
CROSS JOIN table2;
Example; Assign every employee to every department
SELF JOIN A SELF JOIN is when a table joins with itself. You need to use aliases to refer to the same table in two roles.
Example; List all employees and their managers.
NATURAL JOIN A NATURAL JOIN automatically joins tables using all columns that have the same name.
SELECT *
FROM table1
NATURAL JOIN table2;
Example;
Window Functions.
Window functions allow us to perform certain operations on a subset of related rows, called a window, and return a value for each row in that set.
Aggregate window functions are a group of aggregate functions, such as SUM(), COUNT(), AVG(), MAX(), and MIN(), that calculate aggregate values within a window and return a result to each row.
For example, you can use window functions to rank rows, calculate
cumulative totals, or find the difference between consecutive rows in a dataset. Unlike aggregate functions like SUM() or AVG(), which return one result for a group of rows, window functions return a value for each row while still providing information from the related rows.
ROW_NUMBER() Assigns a unique sequential number to each row within a window partition based on the ordering of a column by the ORDER BY clause. No two rows are given the same number.
SELECT
Column_X,
Column_Y,
ROW_NUMBER() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;
RANK() Assigns a rank to each row within a window partition based on the ordering of a column by the ORDER BY clause. Rows with the same values receive the same rank, and the next rank is skipped accordingly.
SELECT
Column_X,
Column_Y,
RANK() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;
DENSE RANK Assigns a rank to each row within a window partition based on the ordering of a column by the ORDER BYclause. Rows with the same values receive the same rank but no ranks are skipped.
SELECT
Column_X,
Column_Y,
DENSE RANK() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;
LEAD Allows the access of a value within a column from the following nth-row relative to the current row. The lead value for the last row within a partition will be NULL since there is nonext value.
SELECT
Column_X,
Column_Y,
Column_Z,
LEAD(Column_Z, n) OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS Alias
FROM
Table_name;
LAG Allows the access of a value within a column from the previous nth-row relative to thecurrent row. The lag value for the first row within a partition will be NULL since there is no previous value.
Top comments (0)