Joins in SQL Server in the presence of Primary and Foreign Key

Rumman Ansari   2020-03-08   Student   SQL SERVER > joins   713 Share

Example:

Code: 

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Join</span><span class="pln"> </span><span class="typ">Examples</span><span class="pln"> </span><span class="kwd">with</span><span class="pln"> primary </span><span class="kwd">and</span><span class="pln"> foreign key

USE </span><span class="typ">SQLExamples</span><span class="pln">

CREATE TABLE </span><span class="typ">Subjects</span><span class="pun">(</span><span class="pln">
	</span><span class="typ">SubjectId</span><span class="pln"> INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">	
	</span><span class="typ">SubjectName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">


INSERT INTO </span><span class="typ">Subjects</span><span class="pln"> VALUES 
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'C'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Java'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Python'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'PHP'</span><span class="pun">)</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">

CREATE TABLE </span><span class="typ">Chapters</span><span class="pun">(</span><span class="pln">
	</span><span class="typ">ChapterId</span><span class="pln"> INT PRIMARY KEY</span><span class="pun">,</span><span class="pln">	
	</span><span class="typ">ChapterName</span><span class="pln"> VARCHAR</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln">
	</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
	FOREIGN KEY </span><span class="pun">(</span><span class="typ">SubjectId</span><span class="pun">)</span><span class="pln"> REFERENCES </span><span class="typ">Subjects</span><span class="pun">(</span><span class="typ">SubjectId</span><span class="pun">)</span><span class="pln">	 
</span><span class="pun">)</span><span class="pln">

INSERT INTO </span><span class="typ">Chapters</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Python'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Python'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction PHP'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">8</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes PHP'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">4</span><span class="pun">)</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">
</span>

Code: Inner Join 

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Inner</span><span class="pln"> </span><span class="typ">Join</span><span class="pln">
DELETE FROM </span><span class="typ">Subjects</span><span class="pln">
DELETE FROM </span><span class="typ">Chapters</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

INSERT INTO </span><span class="typ">Subjects</span><span class="pln"> VALUES 
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'C'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Java'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Python'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'PHP'</span><span class="pun">)</span><span class="pln">


INSERT INTO </span><span class="typ">Chapters</span><span class="pln"> VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes C'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Introduction Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> 

</span>

Code: Inner Join 

<span class="pln"> 

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
INNER JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">


SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Chapters</span><span class="pln">
INNER JOIN </span><span class="typ">Subjects</span><span class="pln">
ON </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">

</span>

Code: Left Join 

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Left</span><span class="pln"> </span><span class="typ">Join</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
LEFT JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">
</span>

Code: Right Join

<span class="pln"> 
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Right</span><span class="pln"> </span><span class="typ">Join</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln">
FROM </span><span class="typ">Subjects</span><span class="pln">
RIGHT JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pun">;</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Full</span><span class="pln"> </span><span class="typ">Outer</span><span class="pln"> </span><span class="typ">Join</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Subjects</span><span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Chapters</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> 
FROM </span><span class="typ">Subjects</span><span class="pln">
FULL OUTER JOIN </span><span class="typ">Chapters</span><span class="pln">
ON </span><span class="typ">Subjects</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Chapters</span><span class="pun">.</span><span class="typ">SubjectId</span><span class="pln">
 
</span>
Share this Post: