DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 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 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery 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 MCQsDBMS Lab Programs
Joins in SQL
Last Updated : 21 May, 2026
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
Unit 2238 words12 headingsTables includedExamples included
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.
Sample Data
| 101 | Alice | 1 | 1 | Computer Science | |
|---|---|---|---|---|---|
| 102 | Bob | 2 | 2 | Mathematics | |
| 103 | Carol | 1 | 3 | Physics |
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 exampleWoHoTech
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 exampleWoHoTech
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 exampleWoHoTech
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 exampleWoHoTech
-- 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 exampleWoHoTech
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 exampleWoHoTech
-- 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 exampleWoHoTech
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 exampleWoHoTech
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 DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, joins, joins in sql
Related 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 level — indepenUnit 2Triggers in SQLA 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 be called manualUnit 2Views in SQLA 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 tables whenevUnit 2Unit 2 — Relational Model & SQLThis unit covers the formal foundations of relational databases and the Structured Query Language SQL. Starting from the mathematical basis of relational algebra and calcUnit 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 sets of rows, Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.