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.

1SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Column = Table2.Column;

1-- Create Students table
2CREATE TABLE Students (
3    StudentID INT PRIMARY KEY,
4    Name VARCHAR(100) NOT NULL
5);
6
7-- Insert sample data into Students table
8INSERT INTO Students (StudentID, Name) VALUES
9(1, 'Harjas'),
10(2, 'Jasraj'),
11(3, 'Mahak');
12
13-- Create Grades table
14CREATE TABLE Grades (
15    StudentID INT,
16    Subject VARCHAR(100) NOT NULL,
17    Grade VARCHAR(2),
18    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
19);
20
21-- Insert sample data into Grades table
22INSERT INTO Grades (StudentID, Subject, Grade) VALUES
23(1, 'Web-based-Programming', 'A'),
24(2, 'DSA', 'B+'),
25(3, 'Maths', 'B'),
26(4, 'EVS', 'A-'),
27(5, 'Dbms', 'A-');
28
29-- Inner Join query to fetch grades of enrolled students
30SELECT Students.Name, Grades.Subject, Grades.Grade
31FROM Students
32INNER JOIN Grades ON Students.StudentID = Grades.StudentID;
33

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

1Left Join: SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.Column = Table2.Column;

1-- Create Employees table
2CREATE TABLE Employees (
3    EmployeeID INT PRIMARY KEY,
4    Name VARCHAR(100) NOT NULL,
5    Profession VARCHAR(100) NOT NULL
6);
7
8-- Insert sample data into Employees table
9INSERT INTO Employees (EmployeeID, Name, Profession) VALUES
10(1, 'Harjas Singh', 'Singer'),
11(2, 'Japanjot Singh', 'Data Scientist'),
12(3, 'Sehaj Bindra', 'Full-stack Developer'),
13(4, 'Jasraj Singh', 'Graphic Designer');
14
15-- Create Projects table
16CREATE TABLE Projects (
17    ProjectID INT PRIMARY KEY,
18    ProjectName VARCHAR(100) NOT NULL,
19    EmployeeID INT,
20    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
21);
22
23-- Insert sample data into Projects table
24INSERT INTO Projects (ProjectID, ProjectName, EmployeeID) VALUES
25(101, 'Concert Tour', 1),
26(102, 'Data Analytics Platform', 2),
27(103, 'E-commerce Website', 3),
28(104, 'Brand Logo Design', 4);
29
30-- Left Join query to fetch all employees and their associated projects
31SELECT Employees.EmployeeID, Employees.Name, Employees.Profession, Projects.ProjectName
32FROM Employees
33LEFT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID
34ORDER BY Employees.EmployeeID;

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

1Right Join: SELECT * FROM Table1 RIGHT JOIN Table2 ON Table1.Column = Table2.Column;

1RIGHT JOIN Employees ON Projects.EmployeeID = Employees.EmployeeID

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.

1-- Create Students table
2CREATE TABLE Students (
3    StudentID INT PRIMARY KEY,
4    Name VARCHAR(100) NOT NULL
5);
6
7-- Insert sample data into Students table
8INSERT INTO Students (StudentID, Name) VALUES
9(1, 'Sehaj'),
10(2, 'Japanjot'),
11(3, 'Manmeet');
12
13-- Create Exams table
14CREATE TABLE Exams (
15    ExamID INT PRIMARY KEY,
16    ExamName VARCHAR(100) NOT NULL
17);
18
19-- Insert sample data into Exams table
20INSERT INTO Exams (ExamID, ExamName) VALUES
21(101, 'DBMS'),
22(102, 'DSA'),
23(103, 'EVS');
24
25-- Create ExamResults table
26CREATE TABLE ExamResults (
27    StudentID INT,
28    ExamID INT,
29    Score INT,
30    PRIMARY KEY (StudentID, ExamID),
31    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
32    FOREIGN KEY (ExamID) REFERENCES Exams(ExamID)
33);
34
35-- Insert sample data into ExamResults table
36INSERT INTO ExamResults (StudentID, ExamID, Score) VALUES
37(1, 101, 85),
38(1, 102, 92),
39(2, 101, 78);
40
41-- Full Outer Join query to retrieve all students with their exam results
42SELECT Students.StudentID, Students.Name, Exams.ExamID, Exams.ExamName, ExamResults.Score
43FROM Students
44FULL OUTER JOIN ExamResults ON Students.StudentID = ExamResults.StudentID
45FULL OUTER JOIN Exams ON ExamResults.ExamID = Exams.ExamID
46ORDER BY Students.StudentID, Exams.ExamID;
47

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

1CREATE VIEW employee_details AS
2SELECT employee_id, first_name, last_name, hire_date
3FROM employees
4WHERE department_id = 10;

• 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

1CREATE SEQUENCE order_seq
2START WITH 1
3INCREMENT BY 1
4NO CYCLE;

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.

1CREATE INDEX idx_last_name
2ON employees (last_name);

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

1 CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE trigger_function();

Example

1CREATE TRIGGER log_employee_changes
2AFTER UPDATE ON employees
3FOR EACH ROW
4BEGIN
5    INSERT INTO employee_changes (employee_id, change_date)
6    VALUES (NEW.employee_id, NOW());
7END;

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)

1Creating a stored procedure: CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END;
2
3Executing a stored procedure: EXEC procedure_name;

Example

1CREATE PROCEDURE get_employee_count()
2BEGIN
3    SELECT COUNT(*) AS total_employees
4    FROM employees;
5END;

Conclusion