Joins

In a relational database management system (DBMS), Joins are fundamental operations used to combine rows from two or more tables based on a related column. The concept of joining tables is crucial in relational databases where data is typically distributed across multiple tables to reduce redundancy and improve data organization.

Purpose:

The primary purpose of joins is to retrieve and present data from multiple tables in a meaningful way. By joining tables, you can create a consolidated result set that contains information from different tables, allowing you to analyses relationships between entities in a database. This is essential for querying complex datasets and extracting valuable insights
 

Types of Joins:

1. Cross joins 
2. Inner joins 
3. Outer Joins 
4. Self joins

We will understand Joins by using two tables:
 

Table 1:       Workers

Table2:        Details

1. CROSS JOIN:

A Cross Join in a relational database management system (DBMS) is an operation that returns the Cartesian product of two or more tables. This means that it combines every row from the first table with every row from the second table, resulting in a complete combination of all possible pairs of rows. The Cross Join is also known as a Cartesian Join.

Syntax:

SELECT * FROM table1 CROSS JOIN table2;

Example:

SELECT * FROM workers CROSS JOIN Details

OUTPUT:

2. INNER JOIN:

It returns only the rows where there is a match in both tables based on the specified condition.

Syntax:

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example:

SELECT * FROM workers
INNER JOIN details ON workers.id = details.id;

Output:
 

3. OUTER JOINS:

It returns all the rows from one table and includes matching rows from the other table. If there is no match for a particular row in the other table, then NULL values are returned for columns from the table without a match.

Types of Outer Joins:

1. Left Join
2. Right Join
3. Full Join

3.1. Left Join:

A Left Join returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, NULL values are returned for columns from the right table.

Syntax:

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example:

Select * FROM WORKERS
LEFT JOIN DETAILS ON WORKERS.ID = DETAILS.ID;

OUTPUT:

3.2. 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 in the left table, NULL values are returned for columns from the left table.

Syntax:

SELECT *  FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example:

SELECT *  FROM workers
RIGHT JOIN details ON workers.id= workers.id;

3.3. FULL Join

A Full join returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table without a match.

Syntax:

SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column:

Example:

SELECT * FROM workers
FULL JOIN details ON workers.id = details.id;

4. SELF Joins

A Self join joins the table with itself, allowing to compare rows within the same table.

Syntax:

SELECT * FROM table1
JOIN table2 ON table1. column = table2.column;

Example

SELECT * FROM table1
JOIN table2 ON table1. column = table2.column;

Note: 
`*` can be replaced by specific columns like workers.age, details.salary etc..