sql join with example

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

SELECT * FROM Courses, STUDENTS 
WHERE
Courses.CID = STUDENTS.S_Courses_id
ORDER BY Courses.CID ASC

CROSS JOIN :-

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

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