_edited_edited.png)
In relational databases, such as Oracle, MySQL, PostgreSQL and others, data are stored across multiple tables related to each other through primary or foreign keys. we often need to access multiple tables and combine data from two or more tables to form a more meaningful result set. We can easily achieve this using SQL joins.
Joins is one the most important concepts in relational databases. SQL JOIN clause are used to query and access data from multiple tables using common key values shared across different tables. It combines rows from two or more tables based on a specific condition and creates a set of rows in a temporary table without altering the structure or data of the base table.
Types Of SQL Joins
There are various types of joins from which we can select a join that is most suitable to our use cases. Now let's look into each of these types with examples.
INNER JOIN
INNER JOIN is the most used type of joins. It returns all the records from both the tables where the join conditions are satisfied.
Visual Representation of SQL INNER JOIN
Syntax for INNER JOIN
SELECT table1.column_name1, table1.column_name2, table2.column_name1,...
FROM table1
INNER JOIN table2
ON table1.matching_column_name = table2.matching_column_name;
or
SELECT table1.column_name1, table1.column_name2, table2.column_name1,...
FROM table1
JOIN table2
ON table1.matching_column_name = table2.matching_column_name
Here,
table1: First table.
table2: Second table
matching_column_name: Column common to both the tables.
SQL INNER JOIN in is same as JOIN clause and can be used interchangeably to achieve the same result.
Example for INNER JOIN
Let's consider two tables, employees and departments tables, which are related by key columns.
Sample Table: employees
Sample Table: departments
Both these tables are connected by one common key (column) i.e department_id.
We can perform an INNER JOIN operation using the given SQL query:
Order of Execution
1. The query starts execution at FROM clause and identifies the employees table as the primary table.
2. Then performs INNER JOIN on employees table with departments table. It combines rows from both the tables based on the condition specified in the ON clause i.e., the values of department_id from both the tables should match.
3. After joining all the rows, employee_id, employee_name columns from employees tables, and all the columns from departments table as specified in the SELECT clause will be extracted and created into a temporary result set.
Output of the Query
In the output we can see that employee_id = 8 from employees table is not retrieved as its corresponding department_id = 6 does not have a matching value in the department_id column of departments table. INNER JOIN selects only those those rows from both the tables that satisfy the condition.
OUTER JOIN
The INNER JOIN returns the matching rows from both of the tables. When using a SQL OUTER JOIN, it not only lists the matching rows, but it also returns the unmatched rows from the other tables based on the left, right or full keywords.
The OUTER JOIN can be categorized based on how they include rows from the participating tables.
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
LEFT JOIN
LEFT JOIN returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there are no matching row on the right side, the result-set will contain NULL. LEFT JOIN is also known as LEFT OUTER JOIN.
Visual Representation of SQL LEFT JOIN
Syntax for LEFT JOIN
SELECT table1.column_name1, table1.column_name2, table2.column_name1,...
FROM table1
LEFT JOIN table2
ON table1.matching_column_name = table2.matching_column_name;
Example for LEFT JOIN
Let's consider the above shown employees and departments tables.
We can perform a LEFT JOIN operation using the given SQL query:
Order of Execution
1. The query starts execution at FROM clause and identifies the employees table as the primary table.
2. Then performs LEFT JOIN on employees table with departments table. It returns all rows from the left table (employees), regardless of whether there are matching rows in the right table (departments). And it fetches only those rows from the right tables based on the condition specified in the ON clause i.e., the values of department_id from both the tables should match.
3. After joining all the rows, employee_id, employee_name columns from employees tables, and all the columns from departments table as specified in the SELECT clause will be extracted and created into a temporary result set.
Output of the Query
In the output we can see that all the rows from right table (employees) are retrieved and only those rows that matched the condition was retrieved from left table (departments). The department_id =5 record has not been fetched from right table as it does not have a matching value in left table. Also, for rows in left table that does not have matching rows in the right table, the result set will contain NULL for the columns from the right table.
RIGHT JOIN
RIGHT JOIN returns all the rows of the table on the right side of the join and matches rows for the table on the left side of the join. For the rows for which there are no matching row on the left side, the result-set will contain NULL. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Visual Representation of SQL RIGHT JOIN
Syntax for RIGHT JOIN
SELECT table1.column_name1, table1.column_name2, table2.column_name1,...
FROM table1
RIGHT JOIN table2
ON table1.matching_column_name = table2.matching_column_name;
Example for RIGHT JOIN
Let's consider the above shown employees and departments tables.
We can perform a RIGHT JOIN operation using the given SQL query:
Order of Execution
1. The query starts execution at FROM clause and identifies the employees table as the primary table.
2. Then performs RIGHT JOIN on employees table with departments table. It returns all rows from the left table (employees) that satisfies the condition specified in the ON clause i.e., the values of department_id from both the tables should match . And it fetches all the rows from the right table (departments) regardless of whether there are matching rows in the left table.
3. After joining all the rows, employee_id, employee_name columns from employees tables, and all the columns from departments table as specified in the SELECT clause will be extracted and created into a temporary result set.
Output of the Query
In the output we can see that all the rows from right table (departments) are retrieved and only those rows that matched the condition was retrieved from left table (employees). The employee_id =8 record has not been fetched from left table as it does not have a matching value in the right table. Also, for rows in right table that does not have matching rows in the left table, the result set will contain NULL for the columns from the left table.
FULL JOIN
A FULL OUTER JOIN is used to combine rows from two or more tables based on a related column between them. It returns all matched rows from both the tables, all unmatched rows from the left table with NULLs for the right table columns and all unmatched rows from the right table with NULLs for the left table columns. To simply put it, FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN.
Visual Representation of SQL FULL JOIN
Syntax for FULL JOIN
SELECT table1.column_name1, table1.column_name2, table2.column_name1,...
FROM table1
FULL JOIN table2
ON table1.matching_column_name = table2.matching_column_name;
Example for FULL JOIN
Let's consider the above shown employees and departments tables.
We can perform a FULL JOIN operation using the given SQL query:
Order of Execution
1. The query starts execution at FROM clause and identifies the employees table as the primary table.
2. Then performs FULL JOIN on employees table with departments table. It returns all rows from both tables, matching them where possible and including NULLs where there is no match.
3. After joining all the rows, employee_id, employee_name columns from employees tables, and all the columns from departments table as specified in the SELECT clause will be extracted and created into a temporary result set.
Output of the Query
In the output we can see that all the rows from both the tables are retrieved. For those rows that does not have a match from the other table, the result set will contain NULL.
CROSS JOIN
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. To put it simple, every row in table1 will be mapped with every row in table2 without checking match conditions.
Visual Representation of SQL CROSS JOIN
Syntax for CROSS JOIN
SELECT *
FROM table1
CROSS JOIN table2;
OR
SELECT *
FROM table1, table2;
Example for CROSS JOIN
Let's consider the above shown employees and departments tables.
We can perform a CROSS JOIN operation using the given SQL query:
Output of the Query
From the output we can see that each row in the employees table is combined with each row in departments table resulting in a dataset where every possible combination of rows is represented, without checking for any matched columns. This is called Cartesian Product. You can see that the total number of rows retrieved is 40, which is the result of multiplying the 8 rows from the employees table by the 5 rows from the departments table.
SELF JOIN
SELF JOIN is used to join a table with itself, especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. It produces a result set where each row of table1 is combined with each row of itself for matched conditions.
Syntax for SELF JOIN
SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_filed = b.common_field;
Aliases (a , b) are applied to differentiate between the two instances of same table
Example for SELF JOIN
Let's consider a new employees tables in which employee_id is the PRIMARY KEY and manager_id is the FOREIGN KEY that references the PRIMARY KEY of the same table (employee_id).
Sample Table: employees
We can perform a SELF JOIN operation using the given SQL query:
Output of the Query
This output shows each employee and their corresponding manager, with both being employees identified by their employee_id. Through this we can see how SELF JOIN demontrates the hierarchical relationships within the same table.
SQL JOINS are the quickest way to combine multiple tables and create a more meaningful dataset. so keep exploring on different types of JOINS to find the best fit for your specific needs.
Comments