PRE-REQUISTES

PART-1 (Understand concept of all keys (theory) and what are anomalies in database)

PART-2 (CRUD operations in database , learn syntax & understand concept by doing)

PART-3 (1st Normal form , 2NF , 3NF , Partial Dependency , Transitive Dependency)

PART-4 (How to group data , Sub-queries & questions)

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

TABLES

I have already created 2 tables named as first and second . Both have same columns. pk is primary key in first table and foreign key in second table.

INNER JOIN

Inner join produces only set of records that match in both tables TABLE A & TABLE B. INNER JOIN is also known as NATURAL JOIN when condition contain '=' operator. Also we can use comparison operator as well like '>''<' .

select * from first INNER JOIN second ON first.pk = second.pk;

LEFT OUTER JOIN

Left Outer Join produces a complete set of records from TABLE A matching records in TABLE B . If their is no match right side will contain nothing means null.

select * from first LEFT OUTER JOIN second ON first.pk = second.pk;

RIGHT OUTER JOIN

Right Outer Join produces a complete set of records from TABLE B matching records in TABLE A . If their is no match left side will contain nothing means null.

select * from first RIGHT OUTER JOIN second ON first.pk=second.pk;

FULL OUTER JOIN

Full Outer Join produces set of all records in Table_A & Table_B with matching records from both sides . If their is no match missing(both) side will contain nothing means null.

NOTE : Full Outer Join is not available in some SQL . It depends on which DBMS you are using . So I will show you how to apply smartly Full Outer Join on tables.

select * from first LEFT OUTER JOIN second 
ON first.pk=second.pk
UNION
select * from first RIGHT OUTER JOIN second
ON first.pk = second.pk

LEFT JOIN EXCLUDING INNER JOIN

It will return all records of the left table which do not match with right table.

select * from first LEFT JOIN second on first.pk = second.pk where second.pk is null;

RIGHT JOIN EXCLUDING INNER JOIN

It will return all records of the right table which do not match with left table.

select * from first RIGHT JOIN second on first.pk = second.pk where first.pk is null;

FULL JOIN EXCLUDING INNER JOIN

select * from first LEFT JOIN second on first.pk = second.pk where second.pk is null
union
select * from first RIGHT JOIN second on first.pk = second.pk where first.pk is null;

<------------END-------------->