The beautiful world of SQL Joins
The beautiful world of joins, let’s dive deeper into it today!
SQL joins are classified into four types based on the expected outcome of combining records, from two or more tables using common columns from the tables involved in the Join function. Inner join is used to find records that have the same values, Left Join is used in both tables to get all records from the left side table and only matching values from the right table, while Right Join is used to get all records from the right table and only matching records in the left table, Finally, a Full Join is performed to retrieve all records from both tables, regardless of whether or not they match.
We’ll learn about Cross, Inner, Self and Outer joins!
Understanding Joins
Why Joins ?
Data division into multiple tables allows for more efficient storage, easier manipulation, and greater scalability. However, these advantages come at a cost.
What is a Join ?
A join is a mechanism for associating or joining tables in a SELECT statement (and thus the name join). You can join multiple tables using a special syntax to return a single set of output, and the join associates the correct rows in each table on the fly.
Note: Understand that a join is not a physical entity; that is, it does not exist in the database tables themselves. The DBMS creates a join as needed and keeps it for the duration of the query execution.
Creating a Join
It is very simple to create a join. You must specify all of the tables that will be included, as well as how they are related to one another.
Example:
What is a Cartesian Product
The results of a table relationship that do not include a join condition. The number of rows retrieved is the sum of the first table’s rows multiplied by the second table’s rows.
What is a Cross Joins
The type of join that returns a Cartesian Product is sometimes referred to as a cross join.
What is Inner Joins
Inner Join is used to return rows from both tables that meet the specified condition. It is the most commonly used join operation and can be thought of as the default join-type.
Inner Join further divided into three subtypes:
- Theta Join
- Natural Join
- EQUI Join
Theta Join
Theta Join allows you to combine two tables based on the theta condition. For all comparison operators, Theta joins work. It is represented by the symbol. A Theta join is the general case of a JOIN operation.
A ⋈ A.column 2 > B.column 2 (B)
EQUI Join
Equijoin — a join based on equality testing between two tables This type of join is also known as an inner join. In fact, for these joins, you can use a slightly different syntax, specifying the type of join explicitly.
When a Theta join uses only the equivalence condition, it is called an EQUI Join. EQUI join is the most difficult operation in an RDBMS to implement efficiently, and it is one of the reasons why RDBMS have critical performance issues.
A ⋈ A.column 2 = B.column 2 (B)
Natural Join
Natural Join makes no use of any comparison operators. The attributes in this type of join should have the same name and domain. There should be at least one common attribute between two relations in Natural Join.
It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes.
Example:
Joining Multiple Tables
There is no limit to the number of tables that can be joined in a SELECT statement in SQL. The fundamental rules for creating the join remain unchanged. List all of the tables first, then define their relationships.
Note: The Postgres max tables to join is 61
Note: Maximum Number of Tables in a Join
There is no limit to the number of tables that can be joined in ANSI SQL, but check for implementations in your DBMS.
Example:
Using Table Aliases
Example:
Note: It is also worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned to the client.
Using Different Join Types
Let’s look at self join, the natural join, and the outer join.
What is Self Joins
As the name implies, the self join joins a table to itself. To use a self join, the table must have a column (let’s call it X) that serves as the primary key and another column (let’s call it Y) that stores values that can be matched up with the values in Column X. Columns X and Y do not have to have the same value for any given row, and Column Y may even be null.
Example:
Assume you wanted to send a mailing to all of Jim Jones’ customer contacts who work for the same company. This query requires you to first determine which company Jim Jones works for, followed by which customers work for that company. One approach to this problem is as follows:
Note: Self joins are frequently used to replace statements that retrieve data from the same table as the outer statement. Although the end result is the same, many DBMSs process joins much faster than subqueries. It is usually worthwhile to test both to see which performs better.
What is Natural Joins
Natural join is one in which only columns that are unique are chosen. For one table, this is typically accomplished by using a wildcard (SELECT *) and explicit subsets of the columns for all other tables.
Natural Join connects two tables that have the same attribute name and datatype. The resulting table will have all of the attributes of both tables, but will only have one copy of each common column.
INNER vs NATURAL joins
Inner Join joins two tables based on the column specified explicitly in the ON clause. The resulting table will include all of the attributes from both tables, including the common column.
Example:
Outer Joins
Most joins relate rows in one table with rows in another. But occasionally, you want to include rows that have no related rows, and thus you use Outer joins.
Example:
Note: SQLite Outer Joins
SQLite supports LEFT OUTER JOIN, but not RIGHT OUTER JOIN. Fortunately, if you do need RIGHT OUTER JOINfunctionality in SQLite, there is a very simple solution just flip the table order.
Full Outer Joins
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Example:
Note: The FULL OUTER JOIN syntax is not supported by MariaDB, MySQL, or SQLite.
Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
Using Joins with Aggregate Functions
Example:
Summary
As you saw, Joins aren’t that scary when you learn them ❤️
I hope you found this article interesting!
