JOIN:

It returns a result set by making the cartesian product of both the tables,and where clause to get the restricted result set according to the condition.

Syntax SELECT [all | distinct ] FROM table_name1 , table_name2 [WHERE condition]
example1 :
SELECT * FROM employee e, dept d WHERE e.id_dept=d.id_dept ;
BY USING JOIN AND USING KEYWORD :
Syntax SELECT [all | distinct ] FROM table_name1 JOIN table_name2 ON|USING condition [WHERE condition]

example :

use of USING keyword When USING keyword is used,then the column name specified must present in both the tables.

SELECT a.*,b.ele FROM test1 a JOIN test2 b USING (par_id) WHERE par_id<>4;
OR
 
SELECT a.*,b.ele FROM test1 a JOIN test2 b ON a.par_id=b. par_id ;
LEFT JOIN :

It returns all the record from left table of join and matching records from the right table according to the join condition.

Syntax SELECT [all | distinct ] FROM table_name1 LEFT JOIN table_name2 ON|USING condition [WHERE condition]

example :
SELECT a.*,b.ele FROM test1 a LEFT JOIN test2 b ON a.id=b. par_id WHERE b.id IS NOT NULL;
OR
 
SELECT a.*,b.ele FROM test1 a LEFT JOIN test2 b USING( par_id) WHERE b.id IS NOT NULL;
RIGHT JOIN :

It returns all the record from right table of join and matching records from the left table according to the join condition.

Syntax SELECT [all | distinct ] FROM table_name1 RIGHT JOIN table_name2 ON|USING condition [WHERE condition]

example :
SELECT a.*,b.ele FROM test1 a RIGHT JOIN test2 b ON a.id=b. par_id WHERE a.id IS NOT NULL;
OR
 
SELECT a.*,b.ele FROM test1 a RIGHT JOIN test2 b USING( par_id) WHERE a.id IS NOT NULL;
INNER JOIN :

It returns the result set from both the tables according to the condition.

Syntax SELECT [all | distinct ] FROM table_name1 INNER JOIN table_name2 ON|USING condition [WHERE condition]

example :
SELECT a.*,b.ele FROM test1 a INNER JOIN test2 b ON a.id=b. par_id WHERE a.id IS NOT NULL;
DELETING AND UPDATING MULTIPLE TABLES THROUGH JOINS :
Syntax DELETE alias1,alias2 FROM table_name alias1 INNER JOIN table_name alias2 ON condition.

example :
DELETE t3,t4 FROM test3 t3 INNER JOIN test4 t4 ON t3.par_id=t4.id;
UPDATE :
Syntax UPDATE table_name alias1 INNER JOIN table_name alias2 ON condition SET alias1.column_name = value , alias2.column_name=value;

example :
UPDATE test3 AS t3 INNER JOIN test4 AS t4 ON t3.par_id=t4.id SET t3.name = 'AFIXI TECHNOLOGY', t4.address = 'NEW M1 IT ZONE,PATIA';