SQL stands for structured Query Language and is the language used to communicate with a database.
What is a database ?
An organized collection of data stored electronically.
Data is stored in tables inside databases.
SQL is used to retrieve data,update and analyse data.
JOINS IN SQL
Joins are used in SQL to combine rows from two or more tables based on a common relationship(primary key)
TYPES OF JOINS
- INNER JOIN It returns rows that are same from both tables. EXAMPLE
From the records this are the matching rows from both tables.
- LEFT JOIN A Left join returns all rows from the left table and the matching rows from the right(second) table. If there are no matching values,NULL values are returned from the right table. EXAMPLE
-
RIGHT JOIN
A right join returns all rows from the right table and the matching rows from the left table.
If there is no match ,NULL values are returned for the columns from first(left) table.
EXAMPLE
From the records all rows in the name column are returned but the department_id and department name for 'Alice' do not have a match hence the NULL values are returned after performing the query.
- Full join It combines the results of a left and right join. A full join returns all rows from both tables, matching rows that meet conditions and includes unmatched rows too. EXAMPLE
NB Some databases like MySQL,MariaDB and SQLite do not support full join and instead a UNION operator is used to join a left and right join.
Joins are very important in SQL since they allow combining of data from different tables,they also reduce duplication by allowing data to be stored in separate tables and they also improve data flexibility.
WINDOW FUNCTIONS IN SQL
Window functions perform calculations on related rows and keeps each row in the result unlike GROUP BY which groups the rows together.
A window function allows viewing of a group of roes.
The OVER() Clause is used in window functions.
Examples of window functions
ROW_NUMBER()RANK()RUNNING TOTALPARTITION BY
Top comments (0)