SQL Joins

Share via:

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 :

 

 

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment