Topics

NULL, Blank Space in SQL Server

Rumman Ansari 2020-03-11 Student SQL SERVER > Insert-Data 2060 Share

Example: Examine with PRIMARY KEY

Code:

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> key </span><span class="kwd">and</span><span class="pln"> </span><span class="typ">Null</span><span class="pln"> </span><span class="kwd">value</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Blank</span><span class="pln"> </span><span class="typ">Space</span><span class="pln">

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

DROP TABLE </span><span class="typ">Item</span><span class="pln">

CREATE TABLE </span><span class="typ">Item</span><span class="pln"> </span><span class="pun">(</span><span class="pln">
    </span><span class="typ">ItemId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> PRIMARY KEY</span><span class="pun">,</span><span class="pln">
    </span><span class="typ">ProductName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">)</span><span class="pln">
 
SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Item</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Normal</span><span class="pln"> execution
INSERT INTO </span><span class="typ">Item</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</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">'Item 1'</span><span class="pun">)</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> possible
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">In</span><span class="pln"> </span><span class="kwd">this</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="typ">ItemId</span><span class="pln"> will be </span><span class="lit">0</span><span class="pln">
 INSERT INTO </span><span class="typ">Item</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
  VALUES</span><span class="pun">(</span><span class="str">''</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Item '</span><span class="pun">)</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> possible
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Cannot</span><span class="pln"> insert the </span><span class="kwd">value</span><span class="pln"> NULL </span><span class="kwd">into</span><span class="pln"> column </span><span class="str">'ItemId'</span><span class="pln">
  INSERT INTO </span><span class="typ">Item</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
  VALUES</span><span class="pun">(</span><span class="str">'Item '</span><span class="pun">)</span><span class="pln">
  
 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> possible
 </span><span class="pun">--</span><span class="pln"> </span><span class="str">'ItemId'</span><span class="pln"> column does </span><span class="kwd">not</span><span class="pln"> allow nulls</span><span class="pun">.</span><span class="pln">
  INSERT INTO </span><span class="typ">Item</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
   VALUES</span><span class="pun">(</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Item 1'</span><span class="pun">)</span><span class="pln">

</span>

Example: Examine without PRIMARY KEY

Code:

<span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Now</span><span class="pln"> we will examine without PRIMARY KEY
  DROP TABLE </span><span class="typ">Item1</span><span class="pln">
   
  CREATE TABLE </span><span class="typ">Item1</span><span class="pln"> </span><span class="pun">(</span><span class="pln">
    </span><span class="typ">ItemId</span><span class="pln"> </span><span class="kwd">int</span><span class="pun">,</span><span class="pln">
    </span><span class="typ">ProductName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">)</span><span class="pln">


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

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Normal</span><span class="pln"> execution 
INSERT INTO </span><span class="typ">Item1</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</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">'Item 1'</span><span class="pun">)</span><span class="pln">

 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> possible
 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">You</span><span class="pln"> can run it multiple times
 INSERT INTO </span><span class="typ">Item1</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
 VALUES</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Item 2'</span><span class="pun">)</span><span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> possible
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">You</span><span class="pln"> can run it multiple times
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">In</span><span class="pln"> </span><span class="kwd">this</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">'ItemId'</span><span class="pln"> will be inserted </span><span class="kwd">as</span><span class="pln"> </span><span class="lit">0</span><span class="pln">
 INSERT INTO </span><span class="typ">Item1</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ItemId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
 VALUES</span><span class="pun">(</span><span class="str">''</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Item 3'</span><span class="pun">)</span><span class="pln">

 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> possible
 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">You</span><span class="pln"> can run it multiple times
 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">In</span><span class="pln"> </span><span class="kwd">this</span><span class="pln"> </span><span class="kwd">case</span><span class="pln"> </span><span class="str">'ItemId'</span><span class="pln"> will be inserted </span><span class="kwd">as</span><span class="pln"> NULL
 INSERT INTO </span><span class="typ">Item1</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ProductName</span><span class="pun">)</span><span class="pln">
 VALUES</span><span class="pun">(</span><span class="str">'Item 4'</span><span class="pun">)</span><span class="pln">

</span>

Example: Joining Without Table Primary key and Foreign Key

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">

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

DROP TABLE </span><span class="typ">Subjects</span><span class="pln">
DROP TABLE </span><span class="typ">Chapters</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</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</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">
</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><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 Java'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</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">
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">

 
</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">
DELETE FROM </span><span class="typ">Subjects</span><span class="pln">
DELETE 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"> 

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">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><span class="pun">--</span><span class="pln"> </span><span class="typ">Example</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> left </span><span class="kwd">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">

 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"> 

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><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">

 DELETE FROM </span><span class="typ">Subjects</span><span class="pln">
 DELETE 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">
 
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">Right</span><span class="pln"> </span><span class="typ">Join</span><span class="pln"> </span><span class="typ">Example</span><span class="pln"> </span><span class="lit">2</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">

 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">

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">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">
 DELETE FROM </span><span class="typ">Subjects</span><span class="pln">
 DELETE 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">
</span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Perl'</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">
</span><span class="pun">(</span><span class="lit">9</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Ruby'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Datatypes Ruby'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5</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">
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>

Example: Joining With Primary and Foreign key

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><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"> 

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

Note: Important

<span class="pln">
</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln">  </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Result</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="lit">1</span><span class="pun">=</span><span class="lit">1</span><span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Output</span><span class="pun">:</span><span class="pln"> </span><span class="lit">1</span><span class="pln">

</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln">  </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Result</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="lit">1</span><span class="pun">=</span><span class="lit">2</span><span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Output</span><span class="pun">:</span><span class="pln"> </span><span class="kwd">no</span><span class="pln">

</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln">  </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Result</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">=</span><span class="kwd">null</span><span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Output</span><span class="pun">:</span><span class="pln"> </span><span class="kwd">no</span><span class="pln">

</span><span class="kwd">select</span><span class="pln"> </span><span class="lit">1</span><span class="pln">  </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Result</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="str">''</span><span class="pun">=</span><span class="str">''</span><span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Output</span><span class="pun">:</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
</span>

Share this Post: