Simple guides to table joins and window functions in SQL.

The postgresql joins clause is used to combine records from two or more tables in a database.A join is a means for combining fields from two tables by using values common to each.
There are five types of joins as shown below;

1.The inner join.
2.The cross join.
3.The left outer join.
4.The right outer join.
5.The full outer join.

THE INNER JOIN

• It creates a new result table by combining column values of two tables(A and B) based upon the join-predicate.
• The query compares each row of table A with each row of table B to find all pairs of rows, which satisfy the join predicate.
• When the join-predicate is satisfied, column values for each matched pair of rows of table A and table B are combined into a result row.
• Its the most common type of join.

The syntax used to perform this kind of join is as shown below;

SELECT A.column1,B.column2 ....
FROM A
INNER JOIN B

The image below shows how an in inner join works.

THE CROSS JOIN

• It matches every row of the first table with every row of the second table.
• If the inputs in tables have x and y columns respectively, the resulting table will have x+y columns.
• Cross joins have the potential to generate extremely large tables and care has to be taken to use them in appropriate. example of synatx used to perform cross joins;

SELECT *
FROM Table1
CROSS JOIN Table2 ;

An image showing an example of a cross join;

THE LEFT OUTER JOIN

• Its an extension of the inner join. SQL standard defines three types of outer joins;LEFT,RIGHT and FULL outer join. -In case of a left outer join an inner join is performed first then each row in table T1 that does not satisfy the join condition with any row in table T2,a joined row is added with null values in columns of T2.So the joined table has at least one row for each row in T1. The following is an example of sql script used to perform left outer join;

SELECT...
FROM Table1
LEFT OUTER JOIN Table 2
ON Conditional expression

An image showing how left outer join operates;

THE RIGHT OUTER JOIN

• For this first an inner join is performed, then for each row in a table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in column of T1.
• Its a converse of left outer join The following is a syntax for right outer join;

SELECT...
FROM Table1 
RIGHT OUTER JOIN Table2
On conditional expression...

The image below show a right outer join performed in a dataset;

FULL OUTER JOIN

• First, an inner join is performed then for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values i column of T2.
• For each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added. The following is a syntax of full outer join;

SELECT...
FROM Table1
FULL OUTER JOIN Table2
ON Conditional expression...

The following image shows a full outer join performed on a dataset;

Windows functions in postgres sql.

• windows function are also known as analytic functions.
• They perform calculations across a set of table rows that are somehow related to the current row.
• They do not collapse rows into a single output instead they allow each row to retain its unique identity while including calculated value as an additional column. Main types of window functions;

1.Ranking functions-These assign a rank or number to rows based on order.
The following re some of rank function under sql;

• Row_Number(): Unique, sequential number for each row. syntax for row number function.

ROW_NUMBER() OVER (
    [PARTITION BY expr1, expr2,...]
    ORDER BY expr1 [ASC | DESC], expr2,...
    )

• Rank() : Ranks with gaps for ties (eg 1,2,2,4).syntax for rank function

RANK() OVER (
    PARTITION BY <expr1>[{,<expr2>...}]
    ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}]
        )

• Dense_Rank():Ranks without gaps for ties.syntax for dense rank

DENSE_RANK() OVER (
   PARTITION BY expression1 [{,expression2...}]
   ORDER BY expression1 [ASC|DESC], [{,expression2...}]
    )

• NTILE(n):It distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. The syntax of the NTILE() function is as follows:

    NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    )

1. Aggregate window function -They are standard math functions used over a window. They are classified into five functions mainly;
2. SUM()-used to calculate the total sum of values within a specified numeric column.It ignores NULL values in the column.syntax for sum is as follows.

 SELECT SUM(column_name) FROM table_name; 

• AVERAGE AVG()-function returns the average value of a numeric column.It ignores the NULL values in the column. Syntax for average function is shown below;

SELECT AVG(column_name)
     FROM table_name
     WHERE condition;

• COUNT ()-it returns the number of rows returned by a query. It counts duplicate rows and rows that contain null values. Syntax for count is as shown below;

SELECT COUNT(column_name) FROM table_name;

• MIN ()- finds the smallest value in a numeric or date column. syntax for min function is shown below;

SELECT MIN(column_name) FROM table_name;  

• MAX() -finds the largest value in a numeric or date column. syntax for max function is shown below;

SELECT MAX(column_name) FROM table_name; 

1. VALUE(Navigation)functions_These functions help to access other rows within the result in the window,therefore helping in comparisons based on the data returned in the column.The following are functions classified under the value functions;

• Lag function provides access to a row at a specified physical offset which comes before the current row.

• You can access data of the previous row, or from the second row before the current row, or from the third row before current row, and so on.
  The following illustrates the syntax of the LAG() function:
  

LAG(return_value [,offset[, default_value ]]) OVER (
    PARTITION BY expr1, expr2,...
    ORDER BY expr1 [ASC | DESC], expr2,...
   )

• first /last value function_It returns data from the first value dispalyed in the window while the last returns data from the last cell displayed in the window. syntax for first value is shown below;

FIRST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )