SQL Joins

What are SQL Joins?

• SQL Joins are used to combine data from two or more tables based on a related column.

Why are Joins Used?

• Combine data from multiple tables

• Reduce duplicate data

• Retrieve related information

• Generate meaningful reports

Types of Joins:

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL JOIN

• SELF JOIN

• CROSS JOIN

INNER JOIN

• The INNER JOIN returns only the records that have matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table 1.column = table2.column

Example:

SELECT Student.Name,
Department.Department
FROM Student
INNER JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

LEFT JOIN

• The LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no match, NULL values are returned.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

SELECT Student.Name,
Department.Department
FROM Student
LEFT JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

RIGHT JOIN

• The RIGHT JOIN returns all records from the right table and the matching records from the left table. If there is no match, NULL values are returned.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example:

SELECT Student.Name,
Department.Department
FROM Student
RIGHT JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

FULL JOIN

• The FULL JOIN returns all records from both tables. If there is no match, NULL values are displayed.

Syntax:

SELECT Student.Name,
Department.Department
FROM Student
INNER JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

Example:

SELECT Student.Name,
Department.Department
FROM Student
FULL JOIN Department
ON Student.Dept_ID = Department.Dept_ID;

SELF JOIN

• A SELF JOIN joins a table with itself.

Example:

SELECT
A.Employee AS Employee,
B.Employee AS Manager
FROM Employee A
INNER JOIN Employee B
ON A.Manager_ID = B.Employee_ID;

CROSS JOIN

• The CROSS JOIN returns every possible combination of rows from two tables.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

SELECT Student.Name,
Department.Department
FROM Student
CROSS JOIN Department;