JOIN, Union Operator

JOIN

Retrieving records from two tables, this should be a column with the same data type and size.

TWO TABLES

TABLE 1 – DEPT1

	DEPTNO	DNAME
1	10	ACCOUNTS
2	20	SALES
3	30	HRD

TABLE 2 – DEPT2

	ENO	ENAME	SAL	DEPTNO
1	1	ANU	11000	10
2	2	UNNI	14000	20
3	3	BALU	21000	20
4	4	ANOOP	31000	10
5	5	ANIL	55000	20
6	6	BIJU	44000	60

INNER JOIN

This will displays only matching records from both the tables

SELECT D.DEPTNO, DNAME, ENAME, SAL, E.DEPTNO FROM DEPT1 AS D INNER JOIN DEPT2 AS E ON D.DEPTNO=E.EDPTNO

(D & E are temporary names (alias), DEPTNO are both in two tables)

LEFT OUTER JOIN

This will display all the records from left table and only matching records from right table.

SELECT D.DEPTNO, DNAME, ENAME, SAL, E.DEPTNO FROM DEPT1 AS D LEFT OUTER JOIN DEPT2 AS E ON D.DEPTNO=E.DEPTNO

RIGHT OUTER JOIN

This will display all the records from right table and only matching records from left table.

SELECT D.DEPTNO, DNAME, ENAME, SAL, E.DEPTNO FROM DEPT1 AS D RIGHT OUTER JOIN DEPT2 AS E ON D.DEPTNO=E.DEPTNO

FULL JOIN

This will display all the records from both the tables

SELECT D.DEPTNO, DNAME, ENAME, SAL, E.DEPTNO FROM DEPT1 AS D LEFT FULL JOIN DEPT2 AS E ON D.DEPTNO=E.DEPTNO

SELF JOIN

This will display all the records from the tables.

UNION OPERATOR

This combines the result of two Queries. Union operator displays distinctive records from two tables excluding duplicate records. Both the tables should have same structure.

Tables

Student1 & Student2

ROLL NO	SNAME
1	MANU
2	AJITH
3	UNNI
4	GOPI

ROLL NO	SNAME
1	MANU
2	AJITH
3	JOHN

SELECT * FROM STUDENT1 UNION SELECT * FROM STUDENT2

UNION ALL

Displays all the Records from both the queries including duplicate value.

SELECT * FROM STUDENT1 UNION ALL SELECT * FROM STUDENT2