Complete SQL join with an example:-
CREATE DATABASE Join_Concepts
USE Join_Concepts
CREATE TABLE Courses
(
CID INT UNIQUE,
CName VARCHAR(30) NOT NULL,
C_dur SMALLINT CHECK (C_dur = 180 OR C_dur = 240)
)
INSERT INTO Courses VALUES (101, 'Ethical Haking', 180), (102, 'Networking',180)
INSERT INTO Courses VALUES (103, 'SQL_DBA', 240)
select * from Courses
TABLE 2:-
CREATE TABLE STUDENTS
(
S_ID INT UNIQUE,
Sname VARCHAR(30) NOT NULL,
Sage TINYINT,
Sgender CHAR CHECK (Sgender IN ('M','F')),
S_Courses_id INT REFERENCES Courses(CID)
)
INSERT INTO STUDENTS VALUES (10001, 'Pooja', 23, 'F', 101),
(10002, 'Shivani', 31, 'F', 101), (10003, 'Palak', 23, 'F', 102),
(10004, 'Neelam', 31, 'F', 102), (10005, 'Monika', 23, 'F', 102),
(10006, 'Varsha', 31, 'F', 102), (10007, 'Rekha', 23, 'F', 101),
(10008, 'Manju', 31, 'F', 101), (10009, 'Shikha', 23, 'F', 101)
select * from STUDENTS
TABLE 3:
CREATE TABLE STAFF
(
STF_ID INT IDENTITY (100001,1) PRIMARY KEY,
STF_name VARCHAR(30) NOT NULL,
STF_age TINYINT,
STF_gender CHAR CHECK (STF_gender IN ('M','F')),
STF_CID INT REFERENCES Courses(CID)
)
INSERT INTO STAFF VALUES ('Nisha', 33, 'F', 101),
('Jeetu', 31, 'M', 101),('Shiva', 39, 'M', 101)
SELECT * FROM Courses -- 3R
SELECT * FROM STUDENTS -- 9R
SELECT * FROM STAFF -- 3R
REQUREMENT #1: Report list of all courses, respective students
SELECT * FROM Courses
SELECT * FROM STUDENTS
SELECT * FROM Courses, STUDENTS -- 27 ROWS. [ 3 COURSES, 9 STUDENTS]
SELECT * FROM Courses, STUDENTS
WHERE
Courses.CID = STUDENTS.S_Courses_id
WHERE
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
SELECT * FROM Courses CROSS JOIN STUDENTS
WHERE -- THIS CONDITION IS EVALUATED / CHECKED AFTER JOIN. SEQUENTIAL
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
ALTERNATIVE SOLUTION TO ABOVE QUERY:
SELECT * FROM Courses INNER JOIN STUDENTS
ON -- THIS CONDITION IS EVALUATED / CHECKED DURING JOIN. PARALLEL
Courses.CID= STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
option (MERGE JOIN)
REQUREMENT #2: Report list of all courses without students
SELECT * FROM Courses LEFT OUTER JOIN STUDENTS
ON -- All left table + matching Right table.
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
SELECT * FROM Courses LEFT OUTER JOIN STUDENTS
ON -- All left table + matching Right table.
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL -- EVALUATED AFTER JOIN
ORDER BY Courses.CID ASC
SELECT Courses.* FROM Courses LEFT OUTER JOIN STUDENTS
ON -- All left table + matching Right table.
-- Non match right is NULL
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL
ORDER BY Courses.CID ASC
SELECT * FROM STUDENTS RIGHT OUTER JOIN Courses
ON
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC
LEFT OUTER JOIN: all left table + matching right table. non-match right is null
SELECT Courses.* FROM Courses LEFT OUTER JOIN STUDENTS
ON
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL
RIGHT OUTER JOIN: all right table + matching left the table. non-match left is null
SELECT Courses.* FROM STUDENTS RIGHT OUTER JOIN Courses
ON
Courses.CID = STUDENTS.S_Courses_id
WHERE STUDENTS.S_Courses_id IS NULL
FULL OUTER JOIN: the combined output of LEFT outer join & RIGHT outer join
SELECT * FROM STUDENTS FULL OUTER JOIN Courses
ON
Courses.CID = STUDENTS.S_Courses_id
===============================================
Q: HOW TO REPORT LIST OF ALL COURSES WITHOUT ANY STUDENTS.
-- MEANS, WE NEED TO LOOK FOR MISSING DATA. HENCE WE NEED TO USE: OUTER JOINS
-- WE CAN USE EITHER LEFT OUTER JOIN OR RIGHT OUTER JOIN OR FULL OUTER JOIN.
----------------------------------------------------------------------------------
LEFT OUTER JOIN: all left table + matching right table. non-match right is null
SELECT C.* FROM Courses AS C LEFT OUTER JOIN STUDENTS AS S
ON
C.CID = S.S_Courses_id
WHERE S.S_Courses_id IS NULL
-----------------------------------------------------------------------------------
RIGHT OUTER JOIN: all right table + matching left the table. non-match left is null
SELECT CRS.* FROM STUDENTS AS ST RIGHT OUTER JOIN Courses AS CRS
ON
CRS.CID = ST.S_Courses_id
WHERE ST.S_Courses_id IS NULL
------------------------------------------------------------------------------------
FULL OUTER JOIN: the combined output of LEFT outer join & RIGHT outer join
SELECT * FROM STUDENTS AS S FULL JOIN Courses C
ON
C.CID = S.S_Courses_id
------------------------------------------------------------------------------------
ALIAS :-
A temporary name given to a table/column/query for easy representation.
alias is not permanent. temporary. operates at statement level only
1 comment:
good of join concept
Post a Comment