Inner joins, Outer Joins, Views, Sequences, Indexes, Triggers

What are Joins in DBMS?

• In SQL, joins are used to bring together information from different tables by finding connections between them.
• This is done by comparing a specific column that is related to the tables.
• By using joins, we can create a new table that
• combines data from multiple tables, making it easier to analyze and understand the information.
• Joins are specified in the SQL query using the JOIN keyword and a join condition that determines how the tables should be linked together.

Types of Joins

• There are different types of joins, such as
• Inner join
• left join
• right join
• full outer join

Inner Joins

• Inner joins are used to merge rows from multiple tables by comparing a related column between them.
• The outcome of an inner join consists solely of the rows that meet the join condition.
• They are used to retrieve only the rows with matching values in both tables, excluding unmatched rows.

• We create the "Students" table to store student information and the "Grades" table to store their grades.
• Sample data is inserted into both tables to represent students and their grades in different subjects.
• The INNER JOIN in the query combines rows from both tables where the "StudentID" matches, i.e.,
• only students with grades are included in the result.
• The result includes columns for student names, subjects, and grades based on the inner join condition.

Outer Joins

• Outer joins are used when we want to retrieve data from one table,
• even if there is no matching data in the other table.
• Three varieties of outer joins exist: left outer joins, right outer joins, and full outer joins.

Types

• Left outer Joins
• Right outer Joins
• Full outer Joins

Left Outer Joins

• A left outer join retrieves or fetches all the rows from the left table and the corresponding matching rows from the right table.
• In cases where there is no match, the right table columns will have NULL values in the result set.

• The LEFT JOIN combines all rows from the left table (Employees) with
• matching rows from the right table (Projects) based on the specified join condition.
• The ON clause specifies the join condition using the "EmployeeID" column to match employees with their projects.
• The ORDER BY clause arranges the result set in ascending order by employee ID for readability.

Right Outer Joins

• A right outer join functions similarly to a left outer join, but with the roles of the tables reversed.
• It gathers all the rows from the right table and pairs them with the corresponding matching rows from the left table.
• In instances where there is no match, the left table columns will have NULL values in the result set.

Full Outer Joins

• A full outer join returns all the rows from both tables and combines them where possible.
• If there is no match, NULL values are returned for the columns of the table that don't have a matching row.
• Suppose you have a "Students" table and an "Exams" table,
• and you want to retrieve all students along with their exam results
• (including students who didn't take any exams and exams that weren't taken by any students).
• A full outer join can achieve this.

• We first create the Students, Exams, and ExamResults tables to store information about students, exams, and their results.
• Sample data is inserted into these tables to represent students, exams, and exam results.
• The FULL OUTER JOIN in the query combines the Students, Exams, and ExamResults tables to retrieve all students along with their exam results.
• The FULL OUTER JOIN ensures that all rows from both the Students and ExamResults tables are included in the result, even if there are no matching rows in the other table.
• The ORDER BY clause is used to sort the results based on StudentID and ExamID for better readability.

Views

• SQL views are like virtual tables created from the result set of a SELECT query,
• allowing you to query them like regular tables without storing the data physically.
• Views represents a subset of data from one or more tables in the database.
• They allow us to simplify complex queries and provide an additional layer of security by granting access to specific columns only.

• In this example, we create a view called "employee_details" that includes specific columns from the "employees" table
• and filters the results based on the department ID.

Usage

Benefits

• Simplified queries: Views allow users to query a subset of data without needing to know the underlying table structures or join complexities.
• Data abstraction: They hide sensitive or unnecessary information, presenting only relevant data to users.

Sequences

• Sequences in SQL are used to generate unique numeric values automatically.
• They are often used for generating primary key values for tables. Here's an instance
• Sequences are objects in DBMS used to generate numeric sequences, typically for generating unique identifiers like primary keys.

Usage

Benefits

• Ensure uniqueness: Sequences guarantee that each generated value is unique within the defined sequence.
• Optimization: They improve performance by preallocating and caching values, reducing overhead when generating new identifiers.

Example

Indexes

Usage

Benefits

• Faster data retrieval: Indexes enable the database to find and retrieve specific rows quickly, especially in large tables.
• Improved query performance: Queries that utilize indexed columns experience faster execution times.

Example

• Creating an index on the "email" column of a "Users" table to speed up searches for users by their email addresses.
• Indexes in SQL are used to improve the performance of queries by providing quick access to data.
• They are created on specific columns of a table and allow the database engine to locate the data more efficiently.

Triggers

• Triggers in SQL are special types of stored procedures that are automatically executed in response to specific events, such as insertions, updates, or deletions of data.
• Triggers can be used to enforce business rules, maintain data integrity, or perform other custom actions. Here's an instance
• Triggers are database objects that automatically perform actions (e.g., executing SQL statements) in response to specific events or changes in the database.

Usage

Benefit

• Data integrity: Triggers help enforce constraints and rules, preventing invalid data modifications.
• Automation: They automate repetitive tasks, such as logging changes or updating related records.

Creating a trigger

Example

Stored Procedures

• Stored procedures in SQL are collections of SQL statements that are precompiled and stored in the database for future execution.
• They are used to encapsulate complex logic and improve code reusability.

Usage

Benefits

• Code reusability: Procedures can be reused across multiple queries or applications, reducing redundancy.
• Modularization: They allow for modular development and organization of database logic.

Syntax (SQL Server)

Example

Conclusion