DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions (MCQs)DBMS Lab Programs
A join combines rows from two or more tables based on a related column between them. Joins are the primary mechanism for querying data spread across...
What is a Join?
A join combines rows from two or more tables based on a related column between them. Joins are the primary mechanism for querying data spread across multiple tables in a normalized database.
1. INNER JOIN
Returns only rows where there is a match in both tables.
| EmpID | Name | DeptID | DeptName |
|---|---|---|---|
| 101 | Alice | 1 | Computer Science |
| 102 | Bob | 2 | Mathematics |
| 103 | Carol | 1 | Computer Science |
INNER JOIN Result (matching rows only)
sql example
SELECT e.EmpID, e.Name, d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID;2. LEFT OUTER JOIN (LEFT JOIN)
Returns all rows from the left table, and matching rows from the right table. If no match, NULLs are used for right-table columns.
| EmpID | Name | DeptID | DeptName |
|---|---|---|---|
| 101 | Alice | 1 | Computer Science |
| 102 | Bob | 2 | Mathematics |
| 103 | Carol | 1 | Computer Science |
LEFT JOIN Result
sql example
SELECT e.EmpID, e.Name, d.DeptName
FROM Employee e
LEFT JOIN Department d ON e.DeptID = d.DeptID;3. RIGHT OUTER JOIN (RIGHT JOIN)
Returns all rows from the right table, and matching rows from the left table. If no match, NULLs for left-table columns.
| EmpID | Name | DeptID | DeptName |
|---|---|---|---|
| 101 | Alice | 1 | Computer Science |
| 102 | Bob | 2 | Mathematics |
| 103 | Carol | 1 | Computer Science |
RIGHT JOIN Result
sql example
SELECT e.EmpID, e.Name, d.DeptName
FROM Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID;4. FULL OUTER JOIN
Returns all rows from both tables. NULLs used where there is no match.
| EmpID | Name | DeptID | DeptName |
|---|---|---|---|
| 101 | Alice | 1 | Computer Science |
| 102 | Bob | 2 | Mathematics |
| 103 | Carol | 1 | Computer Science |
| 104 | David | NULL | NULL |
| NULL | NULL | 3 | Physics |
FULL OUTER JOIN Result
sql example
-- MySQL workaround (no FULL OUTER JOIN natively)
SELECT e.EmpID, e.Name, d.DeptName FROM Employee e
LEFT JOIN Department d ON e.DeptID = d.DeptID
UNION
SELECT e.EmpID, e.Name, d.DeptName FROM Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID;5. CROSS JOIN
Returns the Cartesian product — every combination of rows from both tables.
CROSS JOIN: 4 employees x 3 departments = 12 rows
sql example
SELECT e.Name, d.DeptName
FROM Employee e
CROSS JOIN Department d;Useful for: generating combinations, test data, calendar tables.
6. SELF JOIN
Joins a table with itself. Used for hierarchical or comparative queries within the same table.
sql example
-- Find employee and their manager (both in Employee table)
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ManagerID = m.EmpID;
-- Find pairs of employees in the same department
SELECT A.Name AS Emp1, B.Name AS Emp2, A.DeptID
FROM Employee A
JOIN Employee B ON A.DeptID = B.DeptID AND A.EmpID < B.EmpID;7. NATURAL JOIN
Automatically joins on all columns with the same name in both tables. No ON clause needed.
sql example
SELECT * FROM Employee NATURAL JOIN Department;
-- Joins on DeptID (common column name)Use with caution — column name matches may be unintentional.
Join Types — Visual Summary
| INNER JOIN | Matched rows only |
| LEFT JOIN | All left + matched right |
| RIGHT JOIN | All right + matched left |
| FULL OUTER JOIN | All rows from both sides |
| CROSS JOIN | All combinations |
| SELF JOIN | Table joined with itself |
Joining Multiple Tables
sql example
SELECT e.Name, d.DeptName, p.ProjectTitle
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
JOIN Works_On w ON e.EmpID = w.EmpID
JOIN Project p ON w.ProjID = p.ProjID
WHERE d.DeptName = 'Computer Science'
ORDER BY e.Name;Summary Table
| Join Type | Includes Unmatched Left | Includes Unmatched Right |
|---|---|---|
| INNER | No | No |
| LEFT | Yes | No |
| RIGHT | No | Yes |
| FULL OUTER | Yes | Yes |
| CROSS | N/A (all combinations) | N/A |
Continue the lesson path
Study next in this module
01Unit 2Functions and Stored ProceduresBoth functions and stored procedures are named, reusable blocks of SQL/PL-SQL code stored inside the database. They encapsulate business logic that can be...02Unit 2Nested Queries (Subqueries)A subquery (also called a nested query or inner query) is a SELECT statement embedded inside another SQL statement. The outer query uses the result of the...03Unit 2Domain Relational Calculus (DRC) - Complete TutorialIn-depth guide to Domain Relational Calculus including formulas, expressions, quantifiers, safe formulas, practical examples, and detailed comparison with Tuple Relational Calculus and SQL.04Unit 2Relational AlgebraRelational algebra is a formal, procedural query language for the relational model. It provides the theoretical foundation for SQL. Operations take one or...