[SQL] Basic concepts of query functions

- Inner join vs Outer join

```
INNER JOIN
   ( table1 )∩( table2 )
      [ overlap only ]

(   O   ) (   O   )
       *****  *****    <-- shaded overlap

LEFT JOIN
   ( table1 )⊃( table2 )
      [ all from table1 + overlap ]

(  OOOOO ) (   O   )
   (  OOOOO ) (   O   )
       *****            <-- overlap shaded too

RIGHT JOIN
   ( table1 )⊂( table2 )
      [ all from table2 + overlap ]

(   O   ) ( OOOOO )
   (   O   ) ( OOOOO )
       *****            <-- overlap shaded too

FULL OUTER JOIN
   ( table1 )∪( table2 )
      [ everything from both ]

( OOOOO ) ( OOOOO )
   ( OOOOO ) ( OOOOO )
```

- How window functions works

Sample data

| EmployeeID | Department | Salary |
|------------|------------|--------|
| 1          | HR         | 5000   |
| 2          | HR         | 4000   |
| 3          | IT         | 7000   |
| 4          | IT         | 6000   |
| 5          | IT         | 5000   |

```sql
-- Calculates the average salary per department without grouping the rows (each row still appears).
SELECT 
    EmployeeID,Department,Salary,
    AVG(Salary) OVER (PARTITION BY Department) AS AvgDeptSalary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDept
FROM Employees;
```

Query result

| EmployeeID | Department | Salary | AvgDeptSalary | RankInDept |
|------------|------------|--------|---------------|------------|
| 1          | HR         | 5000   | 4500          | 1          |
| 2          | HR         | 4000   | 4500          | 2          |
| 3          | IT         | 7000   | 6000          | 1          |
| 4          | IT         | 6000   | 6000          | 2          |
| 5          | IT         | 5000   | 6000          | 3          |