SQL JOIN WITH EXAMPLE

                                                         

Create  database  Joiningkey                                // Create Database

Use   Joiningkey;                                                   //Select Database

Create table Join1 (ID INT NOT NULL,  Staff_NAME VARCHAR (20) NOT NULL,  Staff_AGE INT  NOT NULL,   STAFF_ADDRESS VARCHAR (25),  Monthley_Package INT NOT NULL

PRIMARY KEY (ID));                        //Create table Join1

Select * from Join1                                  //Select Table

//Insert Data into Join1 

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (1, 'Aryan', 22, 'Mumbai', 18000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (2, 'Sushil', 32, 'Delhi', 20000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (3, 'Monty', 25, 'Mohali', 22000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (4, 'Amit', 20, 'ALLAHABAD', 12000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (5, 'Anup', 20, 'Pune', 22000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (6, 'Ritesh', 20, 'Bhopal', 16000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (7, 'Amit', 20, 'Mumbai', 12000 );

INSERT INTO Join1 (ID, Staff_NAME,Staff_AGE, STAFF_ADDRESS,Monthley_Package)VALUES (8, 'Vinay', 20, 'Pune', 15000 );

//Create Table   Join2

 Create  table Join2  (Payment_ID INT NOT NULL,  DATE_1 Date NOT NULL,  Staff_ID INT  NOT NULL,  AMOUNT INT NOT NULL

PRIMARY KEY (Payment_ID ));

Select * from Join2;

//Insert Data into Join2 

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (101, '2017-8-21', 1, 3000.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (102, '2007-6-21', 3, 2700.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (103, '2010-8-10', 4, 2500.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (104, '2000-5-21',  9, 2800.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (105, '2011-3-01',  6, 6200.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (106, '2017-5-30',  5, 4100.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (107, '2012-3-01',  2, 3200.00 );

INSERT INTO Join2 (Payment_ID, DATE_1,Staff_ID, AMOUNT)VALUES (108, '2017-6-11',  8, 4500.00 );

Inner Join

Definition: INNER JOIN is used to selects records that have matching values in both tables.

Syntax: SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

Select Staff_ID, Staff_Name, Staff_Age, Monthley_Package, STAFF_ADDRESS, Amount From Join1  a, Join2  b where a.ID = b.Staff_ID;

Left Join

Definition: LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match

Syntax: SELECT column name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

Select  Join1.ID, Join2.Staff_ID, Join1.Monthley_Package, Join2.AMOUNT, join1.STAFF_ADDRESS, Join2.DATE_1,  Join1.Staff_AGE, and  Join2.Payment_ID   from  Join1   left join  Join2  on  Join1.ID = Join2.Staff_ID

 Right Join

Definition:  RIGHT JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Syntax: SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Example:

Select Join1.ID, Join2.Staff_ID, Join1.Monthley_Package, Join2.AMOUNT, join1.STAFF_ADDRESS,  Join2.DATE_1,  Join1.Staff_AGE, and  Join2.Payment_ID from Join1

right join  Join2 on Join1.ID = Join2.Staff_ID Order by Payment ID

 Full Join

Definition: FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Example:

Select * from Join1 Full Outer Join  Join2  on Join1.ID = Join2.Staff_ID

 

Cross Join

Definition: It is used to combine each row of first table with each row from the second table, known as Cartesian join or cross join

Syntax:

 SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]  

           OR  

 SELECT * FROM [ TABLE1] , [TABLE2]  

Example:

  Select * from Join1 Cross Join  Join2

    OR

  Select * from Join1, Join2

 

How to store final result in new table (Result) after joining two tables (join1 and join2)

 Select * into Result from Join1  left outer join  Join2  on Join1.ID = Join2.Staff_ID

 Select * from Result