Hi Oracle folks,
Today, we will talk about different types of Joins in Oracle. Join is one of the most critical topics in SQL which is little hard to understand but very useful. So, stay tuned, I will try to explain it as simple as possible.
So the first thing first, what is a Join?
A Join is query that helps to fetch data from one or more tables, views (simple views or materialized views). Whenever we fetch data from multiple tables, database performs a specific join among those tables and shows the selected columns even if user do not mentioned it in the query.
While working with joins, we must use TABLE ALIAS and reference them to the columns throughout the query. It helps us to avoid column ambiguity and helps to read and understand the query.
Join queries must contain at least one join condition. It can be either in the FROM clause or in the WHERE clause of the query. Join condition means, there should be at least one column in each table with the similar values. Oracle database compare these columns from each table and produce an output from both (if join between two tables only) the table.
There are two types of Join condition.
Some blogs/tutorials may confuse you by saying that Equijoin is a type of Join but it is not. Equijoin is a join condition. When Join condition is evaluated using equality operator (=) that join is called Equijoin.
Opposite to Equijoin, when join condition is evaluated using any of non-equality operators (>, <, <= etc.), it is called Non-Equijoin condition. Most of the times Joins are performed based on the Equijoin condition.
If we omit this join condition Oracle database performs a different type of Join which is CARTESIAN PRODUCTS and produce an output. We will talk about this join in detail in a separate blog.
To the context of usability, there are 2 types of Join and Outer Join is further divided into 3 joins. See the chart below.
To keep it simple to understand, I will write one blog per Join. So in the upcoming blogs we will talk about these joins in very detail with some practical examples.
FROM TABLE1 A <join> TABLE2 B
<join condition> [WHERE …];
I hope you have understood Join in SQL!
If you have any questions/suggestions, please comment below or write me an email.