Dear Readers,
In this article, we will see the SQL Joins.
Joins
A join is a query that combines rows from two or more tables
Join Conditions
Most join queries contain WHERE clause conditions that compare two columns, each from a different table.
Such a condition is called a join condition.
Equijoins
An equijoin is a join with a join condition containing an equality operator (=).
An equijoin combines rows that have equivalent values for the specified columns.
Example : Here we are joining two tables (Emp, Dept).
To get the selected columns from two tables
To get all the information from two tables
If we want to select the columns present in both the tables we have to specify from which table we’re picking
For example Deptno column present in both the tables, So we have to specify which deptno we’re selecting
We can write the above query using Alias names
In the above query, E & D are alias names for the tables EMP & DEPT
Below query gives the list of employees who are working in department SALES
More Examples :
Non-Equi Joins:
Non-equi joins is used to return result from two or more tables where the exact join is not possible.
To get all the information of the employees along with salary grade and high salary, low salary
The Below query gives the employee grades based on their salaries
(Or)
Joining three tables (EMP, DEPT, SALGRADE)
SELF JOIN
A self-join is a join of a table to itself.
To get the list of employee names and their managers
To display the MGR ename as Manager
To get the list of employees who are working under manager KING
To get the manager name of employee BLAKE
INNER JOIN
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate.
The basic syntax of INNER JOIN is as follows:
Example:
(Or)
We can also write INNER JOIN as JOIN
LEFT JOIN:
This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER
The basic syntax of LEFT JOIN is as follows:
Example:
RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
Example :
FULL JOIN
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
Syntax:
Example :
Cartesian Products :
If two tables in a join query have no join condition, Oracle returns their Cartesian product.
Oracle combines each row of one table with each row of the other.
A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows.
Always include a join condition unless you specifically need a Cartesian product.
The basic syntax of Cartesian products is as follows:
Example :