SQL Joins

SQL Join

SQL joins are used to combine rows from two or more tables based on a related column between them. Joins are fundamental in relational databases as they enable the retrieval of data from multiple tables simultaneously, allowing for complex data analysis and reporting. There are different types of SQL joins:

1. Inner Join: An inner join returns only the rows that have matching values in both tables based on the specified condition. The syntax for an inner join is:




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



2. Left Join (or Left Outer Join): A left join returns all rows from the left table (the first table mentioned in the query) and the matched rows from the right table. If there is no matching row in the right table, NULL values are returned for the columns from the right table. The syntax for a left join is:




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



3. Right Join (or Right Outer Join): A right join returns all rows from the right table and the matched rows from the left table. If there is no matching row in the left table, NULL values are returned for the columns from the left table. The syntax for a right join is:




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



4. Self join

A self join in SQL is a join operation where a table is joined with itself. This is useful when you need to compare rows within the same table or when you want to create hierarchical relationships within a table.

The syntax for a self join is similar to that of any other join, but you must use aliases to distinguish between the different instances of the same table.

Here's the general syntax for a self join:




SELECT t1.column1, t1.column2, ..., t2.column1, t2.column2, ... FROM table AS t1 JOIN table AS t2 ON t1.related_column = t2.related_column;

5. Full Join (or Full Outer Join): A full join returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for the columns from the table that lacks a matching row. The syntax for a full join varies between database systems. Some use FULL JOIN, while others use OUTER JOIN:




-- Using FULL JOIN
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
-- Using OUTER JOIN
SELECT columns
FROM table1
LEFT OUTER JOIN table2 ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT OUTER JOIN table2 ON table1.column = table2.column;



6. Cross Join: A cross join returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables. The syntax for a cross join is:




SELECT columns
FROM table1
CROSS JOIN table2;

Here's a simple example to illustrate an inner join:

Suppose you have two tables: employees and departments. The employees table contains information about employees, including their department ID (dept_id), and the departments table contains information about departments, including their department ID (dept_id). To retrieve the employees' names along with their department names, you can perform an inner join:

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

This query will return the first name and last name of each employee, along with the name of the department they belong to.