SQL JOIN

SQL JOIN

SQL JOIN is used to combine rows from two or more tables based on a related column between them. This command is essential for retrieving related data stored in multiple tables, allowing for more comprehensive queries and reports.

Types of SQL JOINs

  • 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. The result is NULL from the right side, if there is no match.
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table. The result is NULL from the left side, when there is no match.
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table. The result is NULL from one side, when there is no match.
  • CROSS JOIN: Returns the Cartesian product of both tables, i.e., pairs each row of the first table with each row of the second table.

Syntax

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

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

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

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

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

Example SQL JOIN Queries

Let's look at some examples of SQL JOIN queries using the employees and departments tables:

1. INNER JOIN Example

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

This query retrieves the first name, last name, and department name of employees who have a matching department_id in the departments table.

2. LEFT JOIN Example

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

This query retrieves all employees' first name and last name, along with their department name if available. If an employee does not belong to a department, the department_name will be NULL.

3. RIGHT JOIN Example

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

This query retrieves all departments and their employees. If a department has no employees, the first name and last name will be NULL.

4. FULL JOIN Example

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

This query retrieves all employees and departments, including those without a match in the other table.

5. CROSS JOIN Example

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
CROSS JOIN departments;

This query retrieves the Cartesian product of the employees and departments tables, pairing each employee with each department.

Conclusion

SQL JOINs are powerful tools for combining related data from multiple tables. Understanding the different types of JOINs and their syntax is essential for writing effective and efficient queries that retrieve comprehensive and meaningful data from a relational database.