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.
Joined relations involve combining data from two or more tables based on a related column. SQL provides several types of joins to accomplish this:
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;
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;
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;
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;
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 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 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;
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;