Relations (Joined and Derived)

JOINED AND DERIVED RELATION 

In SQL, relations refer to how data from multiple tables is combined or derived to create a meaningful result set. There are primarily two types of relations: joined relations and derived relations.

1. Joined Relations(JOIN):

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. 

 

 

INNER JOIN:

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

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

Example:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (or LEFT OUTER JOIN):

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

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

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

RIGHT JOIN (or RIGHT OUTER JOIN):

The 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 columns from the left table.

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

FULL JOIN (or FULL OUTER JOIN):

The 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.

SELECT *
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

EXAMPLE OF JOIN

EMPLOYEE ADDRESS

 

1. INNER JOIN 

INNER JOIN: Returns only the rows where there is a match in both tables.

SELECT Employee.Name, Address.City FROM Employee

INNER JOIN Address

ON Employee.ID = Address.ID;

 

2. LEFT JOIN

LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.

SELECT Employee.Name, Address.City FROM Employee

LEFT JOIN Address

ON Employee.ID = Address.ID;

3. RIGHT JOIN

RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.

SELECT Employee.Name, Address.City FROM Employee

RIGHT JOIN Address

ON Employee.ID = Address.ID;

4. FULL JOIN

FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in one of the tables. If there's no match, NULL values are returned for columns from the table without a match.

SELECT Employee.Name, Address.City FROM Employee

FULL JOIN Address

ON Employee.ID = Address.ID;

 

2. Derived Relations:

Derived relations involve creating a new set of data based on the result of a query or operation. This can include using sub-queries, aggregations, or transformations to derive meaningful information.

Sub-Queries:

Sub-queries are queries embedded within another query. They can be used to derive data dynamically.

Example:

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');

Aggregations:

Aggregations involve deriving summary information from a set of rows, often using functions like COUNT, SUM, AVG, etc.

Example:

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Transformations:

Derived relations can involve transforming or manipulating data in various ways, such as using functions or calculations.

Example:

SELECT employee_name, salary * 1.1 AS increased_salary
FROM employees;