SQL Joins

SQL Joins are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data from multiple tables in a single query.

Types of Joins

There are several types of SQL joins:

• INNER JOIN: Retrieves rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Retrieves all rows from the left table and matched rows from the right table. If no match, NULL values are returned for columns from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Retrieves all rows from the right table and matched rows from the left table. If no match, NULL values are returned for columns from the left table.
• FULL JOIN (or FULL OUTER JOIN): Retrieves all rows when there is a match in one of the tables. Non-matching rows will have NULL values in columns from the table where there is no match.
• CROSS JOIN: Retrieves the Cartesian product of the two tables, meaning all possible combinations of rows from both tables.

Syntax

The basic syntax for each join type is:

-- INNER JOIN
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

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

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

-- FULL JOIN
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

-- CROSS JOIN
SELECT columns
FROM table1
CROSS JOIN table2;

        

Examples

Here are some examples of SQL joins:

-- INNER JOIN Example
SELECT Employees.name, Departments.department_name
FROM Employees
INNER JOIN Departments
ON Employees.department_id = Departments.id;

-- LEFT JOIN Example
SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments
ON Employees.department_id = Departments.id;

-- RIGHT JOIN Example
SELECT Employees.name, Departments.department_name
FROM Employees
RIGHT JOIN Departments
ON Employees.department_id = Departments.id;

-- FULL JOIN Example
SELECT Employees.name, Departments.department_name
FROM Employees
FULL JOIN Departments
ON Employees.department_id = Departments.id;

-- CROSS JOIN Example
SELECT Employees.name, Departments.department_name
FROM Employees
CROSS JOIN Departments;