SQL Joins | Inner join | Left Join | Right Join | Full Join | Self Join

SQL JOIN:

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

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

INNER JOIN:

Returns 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 * FROM STUDENT

INNER JOIN COURSE

OJ STUDENT.ID =COURSE.ID;

Left Join:

Returns all records from the left table and the matched records from the right table.

Syntax:

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

Example:

SELECT * FROM STUDENT AS S

LEFT JOIN COURSE C

ON S.ID =C.ID

Right Join:

Returns all records from the right table and the matched records from thebleft table.

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

Example:

SELECT * FROM STUDENT AS S

RIGHT JOIN COURSE C

ON S.ID =C.ID

Full Join:

Returns all records when there is a match in either left or right table.

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

Example:

Select * from student s

Full join course c

On s.id =c.id

Self join:

A self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Or

Select columns

From table as a

JOIN table as b

On a.col_name = b.col_name;

Example

Select a.name as manager_name, b.name

From employee as a 

Join employee as b

On a.id =v

Manager_id;

Left and right exclusive joins:

Left exclusive join:

Select * from student as a

Left join course as b

On a.id = b.id

Where b.id is null