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 DBMSBig Data and 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 |
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Joins in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, joins, sql, joins in sql
Related Database Management Systems (DBMS) Topics
Continue learning this concept
Unit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database...Unit 2Triggers in SQL — Database Management SystemsA trigger is a stored procedure that automatically executes (fires) in response to specific events on a table or view. Triggers are event-driven and cannot...Unit 2Views in SQL — Database Management SystemsA view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base...Unit 2SQL IntroductionSQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users to define, manipulate, and control data...Unit 2Unit 2 — Relational Model & SQLThis unit forms the backbone of practical database work. While Unit 1 introduced conceptual modeling with ER diagrams, this unit translates those conceptual designs into Unit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on...