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