Joins
#Joins
A join in SQL is a relational database operation used to combine rows from two or more tables based on a related
column between them . Joins allow for efficient data retrieval and are essential for generating meaningful insights
and solving complex business queries. The sources provide an example of how joins can be used to combine data
from the 'Student' and 'StudentCourse' tables to retrieve information such as student details and their enrolled
courses.
Types of Joins
The sources describe various types of joins, each serving a specific purpose:
Natural Join : Combines tuples from two relations where the values of common attributes are equal, including
the common attribute(s) only once in the result . For example, in, a natural join between the 'student' and
'Subject' tables, based on the common attribute 'R_no', would produce a table containing the 'R_no', 'Name', and
'S_id' attributes, where the 'R_no' values match in both original tables.
Equi-Join : Similar to the natural join but explicitly specifies an equality condition for joining. It is the most
common type of join . For example, if you have a 'Drivers' table with driver information and a 'Cars' table with car
information, an equi-join could be used to retrieve information about drivers and their assigned cars by matching
the 'DriverID' column in the 'Drivers' table with the 'AssignedDriverID' column in the 'Cars' table.
Theta Join : A general form of join that combines tuples based on any specified condition (theta predicate).
This allows for more flexible comparisons beyond equality . For example, if you want to find all employees who
earn more than a certain salary, you can use a theta join with the condition "Salary > 50000" to combine the
'Employees' table with itself, comparing the 'Salary' column of each row.
Inner Join : Returns only the rows from both tables where the join condition is met . This is the default join
type in SQL . For example, an inner join between the 'Orders' and 'Customers' tables, based on the common
'CustomerID' column, would return only the orders that have a matching customer in the 'Customers' table.
Outer Join : Returns all rows from one or both tables, even if there are no matching rows in the other table. This
can be further categorised into:
Left Outer Join : Preserves all rows from the left table and matching rows from the right table, padding
non-matching rows from the left table with null values . For example, in, a left outer join between the tables
'A' and 'B', based on common values in 'R_no', would include all records from table 'A', even those with
'R_no' values not present in table 'B', with null values for corresponding 'S_id' attributes.
Right Outer Join : Preserves all rows from the right table and matching rows from the left table, padding
non-matching rows from the right table with null values . For example, in, a right outer join between the
tables, based on common values in 'S_id', would include all records from the right table, even those with
'S_id' values not present in the left table, with null values for corresponding 'R_no' attributes.
Full Outer Join : Returns all rows from both tables, padding non-matching rows from either table with null
values. For example, in, a full outer join of the tables, based on common values in 'S_id', would include all
rows from both tables. Rows in the left table without a match in 'S_id' would have a null 'S_id', and rows in
the right table without a match in 'S_id' would have a null 'R_no'. notes that a full outer join can be
accomplished by taking the union of a left and right outer join.
F ullOuterJ oin = Lef tOuterJ oinU RightOuterJ oin
Cartesian Join/Cross Product : Combines each row from one table with every row from another table . This
often results in a very large result set and is rarely used in practice. For example, in, a cross product between the
'Depositor' and 'Borrower' tables would produce a new table with all possible combinations of rows from both
original tables, regardless of any matching attributes.
Self Join : A regular join that combines rows from a table with itself, based on certain conditions . This is useful
for comparing rows within the same table. For example, in, self joins are presented as an alternative to using
subqueries, allowing you to compare different rows within the same table.
It's also important to note that the SQL standard defines how many copies of each tuple are present in the output of a
query. To model this, a multiset relational algebra, which works on multisets (sets that can contain duplicates), is
used. This differs from relational algebra, which works on sets without duplicates.
The specific type of join and its corresponding condition should be selected based on the desired outcome and the
relationships between the tables involved.
