NULL, Blank Space in SQL Server

Example: Examine with PRIMARY KEY

Code:

-- Primary key and Null value, Blank Space

USE SQLExamples

DROP TABLE Item

CREATE TABLE Item (
    ItemId int PRIMARY KEY,
    ProductName varchar(255), 
)
 
SELECT * FROM Item

-- Normal execution
INSERT INTO Item (ItemId, ProductName)
 VALUES(1, 'Item 1')

-- This is possible
-- In this case ItemId will be 0
 INSERT INTO Item (ItemId, ProductName)
  VALUES('', 'Item ')

-- This is not possible
-- Cannot insert the value NULL into column 'ItemId'
  INSERT INTO Item (ProductName)
  VALUES('Item ')
  
 -- This is not possible
 -- 'ItemId' column does not allow nulls.
  INSERT INTO Item (ItemId, ProductName)
   VALUES( NULL, 'Item 1')

Example: Examine without PRIMARY KEY

Code:

-- Now we will examine without PRIMARY KEY
  DROP TABLE Item1
   
  CREATE TABLE Item1 (
    ItemId int,
    ProductName varchar(255), 
)


SELECT * FROM Item1

-- Normal execution 
INSERT INTO Item1 (ItemId, ProductName)
 VALUES(1, 'Item 1')

 -- This is possible
 -- You can run it multiple times
 INSERT INTO Item1 (ItemId, ProductName)
 VALUES(NULL, 'Item 2')

-- This is possible
-- You can run it multiple times
-- In this case 'ItemId' will be inserted as 0
 INSERT INTO Item1 (ItemId, ProductName)
 VALUES('', 'Item 3')

 -- This is possible
 -- You can run it multiple times
 -- In this case 'ItemId' will be inserted as NULL
 INSERT INTO Item1 (ProductName)
 VALUES('Item 4')

Example: Joining Without Table Primary key and Foreign Key

Code:

-- Join Examples

USE SQLExamples

DROP TABLE Subjects
DROP TABLE Chapters

CREATE TABLE Subjects(
	SubjectId INT,	
	SubjectName VARCHAR(30)
)


INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT,	
	ChapterName VARCHAR(30),
	SubjectId int,
)

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4)

SELECT * FROM Chapters


-- Inner Join
DELETE FROM Subjects
DELETE FROM Chapters

SELECT * FROM Subjects
SELECT * FROM Chapters

INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')


INSERT INTO Chapters VALUES
(1, 'Introduction C', 1), 
(2, 'Datatypes Java', 2) 

SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
INNER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;

 
-- Left Join
DELETE FROM Subjects
DELETE FROM Chapters

INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java') 

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4)


SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
LEFT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;

 -- Example 2 - left join
 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2) 

SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
LEFT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;


-- Right Join

 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2)
 
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
RIGHT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;

-- Right Join Example 2
 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java')

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4)
 
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
RIGHT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;





-- Full Outer Join
 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP'),
(6, 'Perl')

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4),
(9, 'Datatypes Ruby', 5),
(10, 'Datatypes Ruby', 5)

SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT * 
FROM Subjects
FULL OUTER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId
 

Example: Joining With Primary and Foreign key

Code:

-- Join Examples with primary and foreign key

USE SQLExamples

CREATE TABLE Subjects(
	SubjectId INT PRIMARY KEY,	
	SubjectName VARCHAR(30)
)


INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT PRIMARY KEY,	
	ChapterName VARCHAR(30),
	SubjectId int,
	FOREIGN KEY (SubjectId) REFERENCES Subjects(SubjectId)	 
)

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4)

SELECT * FROM Chapters


-- Inner Join
DELETE FROM Subjects
DELETE FROM Chapters

SELECT * FROM Subjects
SELECT * FROM Chapters

INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')


INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2) 

SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
INNER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;


SELECT *
FROM Chapters
INNER JOIN Subjects
ON Chapters.SubjectId = Subjects.SubjectId;



-- Left Join
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
LEFT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;


-- Right Join
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT *
FROM Subjects
RIGHT JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;

-- Full Outer Join
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT * 
FROM Subjects
FULL OUTER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId
 

Note: Important

select 1  as Result where 1=1
-- Output: 1

select 1  as Result where 1=2
-- Output: no

select 1  as Result where null=null
-- Output: no

select 1  as Result where ''=''
-- Output: 1