SQL Joins

/*CREATE TABLE tbl_Employee_Data*/

CREATE TABLE tbl_Employee_Data
(
 EMPID           INT,
 FIRST_NAME      VARCHAR(50),
 MIDDLE_NAME     VARCHAR(50),
 LAST_NAME       VARCHAR(50),
 NATIONALITY     VARCHAR(50)
);

INSERT INTO dbo.tbl_Employee_Data
       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY)

VALUES (101, 'SUBAL', 'CHANDRA', 'DAS', 'INDIAN');

INSERT INTO dbo.tbl_Employee_Data
       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY)

VALUES (103, 'RANGA', 'SELAM', 'REDDY', 'INDIAN');

INSERT INTO dbo.tbl_Employee_Data
       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY)

VALUES (104, 'SUBHANI', 'SK', 'MAHABOOB', 'INDIAN');

INSERT INTO dbo.tbl_Employee_Data
       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY)

VALUES (106, 'BHARGHAVI', 'CHANDRA', 'RAVULAPALLI', 'INDIAN');

INSERT INTO dbo.tbl_Employee_Data
       (EMPID, FIRST_NAME, MIDDLE_NAME, LAST_NAME,NATIONALITY)

VALUES (108, 'NAGA', 'RANI', 'DAS', 'INDIAN');

SELECT * FROM tbl_Employee_Data;

/*CREATE TABLE tbl_Employee_Details*/
CREATE TABLE tbl_Employee_Details
(
 EMPID           INT,
 DOB             DATETIME,
 CURRENT_FLAG    INT,
 STARTDATE       DATETIME,
 ENDDATE         DATETIME DEFAULT('01-01-2019')
);

INSERT INTO dbo.tbl_Employee_Details
       (EMPID, DOB, CURRENT_FLAG, STARTDATE)

VALUES (101,'12-07-1945', 0, '01-01-2010');

INSERT INTO dbo.tbl_Employee_Details
       (EMPID, DOB, CURRENT_FLAG, STARTDATE)
VALUES (102, '12-17-1974', 1, '07-01-2010');

INSERT INTO dbo.tbl_Employee_Details
       (EMPID, DOB, CURRENT_FLAG, STARTDATE)      
VALUES(103,'05-11-1947', 0, '02-02-2010');
     
INSERT INTO dbo.tbl_Employee_Details
       (EMPID, DOB, CURRENT_FLAG, STARTDATE)      
VALUES(104,'12-11-1977', 0, '07-01-2010');
     
INSERT INTO dbo.tbl_Employee_Details
       (EMPID, DOB, CURRENT_FLAG, STARTDATE)      
VALUES(105,'12-07-1987',1, '01-11-2010');

SELECT * FROM tbl_Employee_Details

/*CREATE TABLE tbl_Employees_Name*/

CREATE TABLE tbl_Employees_Name
(
 FIRST_NAME      VARCHAR(50),
 MARITAL_STATUS  CHAR(1),
 EMPLOYEE_GENDER CHAR(1),
 );


INSERT INTO dbo.tbl_Employees_Name
       (FIRST_NAME, MARITAL_STATUS,EMPLOYEE_GENDER)

VALUES ('SUBAL','M','M');

INSERT INTO dbo.tbl_Employees_Name
       (FIRST_NAME, MARITAL_STATUS,EMPLOYEE_GENDER)

VALUES ('RANGA','S','M');

INSERT INTO dbo.tbl_Employees_Name
       (FIRST_NAME,MARITAL_STATUS,EMPLOYEE_GENDER)

VALUES ('SUREKHA','S','F');

INSERT INTO dbo.tbl_Employees_Name
       (FIRST_NAME,MARITAL_STATUS,EMPLOYEE_GENDER)

VALUES ('VISHNU', 'S','M');

INSERT INTO dbo.tbl_Employees_Name
       (FIRST_NAME,MARITAL_STATUS,EMPLOYEE_GENDER)

VALUES ('NAGA','S','F');

SELECT * FROM tbl_Employees_Name

/* SQL JOINS*/
-- INNER JOIN
Inner join returns only those records/rows that match/exists in both the tables.

 Syntax:  SELECT COLUMNS FORM TABLE1
               INNER JOIN TABLE2
              ON
              TABLE1.COLUMN=TABLE2.COLUMN

SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A
INNER JOIN tbl_Employee_Details B
ON
A.EMPID=B.EMPID

--Alternative INNER JOIN
SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A,tbl_Employee_Details B
WHERE  A.EMPID=B.EMPID

-- OUTER JOIN
-- RIGHT OUTER JOIN
Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. 

Syn: SELECT COLUMNS FORM TABLE1
      RIGHT JOIN TABLE2
      ON
      TABLE1.COLUMN=TABLE2.COLUMN

SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A
RIGHT JOIN tbl_Employee_Details B
ON
A.EMPID=B.EMPID

-- LEFT OUTER JOIN
.Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Syn: SELECT COLUMNS FORM TABLE1
      LEFT JOIN TABLE2
      ON
      TABLE1.COLUMN=TABLE2.COLUMN

SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A
LEFT JOIN tbl_Employee_Details B
ON
A.EMPID=B.EMPID

-- FULL OUTER JOIN
Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables.If there are no columns matching in the both tables, it returns NULL values.

Syn: SELECT COLUMNS FORM TABLE1
      FULL JOIN TABLE2
      ON
      TABLE1.COLUMN=TABLE2.COLUMN

SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A
FULL JOIN tbl_Employee_Details B
ON
A.EMPID=B.EMPID

-- CROSS JOIN
Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

Syn: SELECT COLUMNS FORM TABLE1
     CROSS JOIN TABLE2
   
SELECT * FROM tbl_Employee_Data
CROSS JOIN
tbl_Employee_Details

/*MULTIPLE TABLES JOIN*/
SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
       C.MARITAL_STATUS,C.EMPLOYEE_GENDER,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A
INNER JOIN tbl_Employee_Details B
ON
A.EMPID=B.EMPID
INNER JOIN tbl_Employees_Name C
ON
A.FIRST_NAME=C.FIRST_NAME

/*MULTIPLE TABLES WITHOUT JOIN*/
SELECT A.EMPID, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME,A.NATIONALITY,
       C.MARITAL_STATUS,C.EMPLOYEE_GENDER,
  B.DOB, B.CURRENT_FLAG, B.STARTDATE,B.ENDDATE
FROM   tbl_Employee_Data A,tbl_Employees_Name C,tbl_Employee_Details B
WHERE
A.EMPID=B.EMPID
AND
A.FIRST_NAME=C.FIRST_NAME